JL Computer Consultancy

SQL*Plus has no natural way of passing data from the database to a shell script, other than the exit code, which is limited to a a single byte value. Consequently developers are endlessly imaginative about methods for doing this job. This is a method which I think it very elegant, but which I have never seen anyone else use.

In most flavours of Unix it is now possible to handle single-dimension arrays. These are populated with the set command with the -A option, e.g.

        set -A m_var x y z

which would result in a single variable (m_var) being created with the subscripted values:

        ${m_var[1]} = 'x'

        ${m_var[2]} = 'y'

        ${m_var[3]} = 'z'

If your Unix shell can handle this, then you need only execute something like:

        set -A m_var `sqlplus -s uid/pw @script.sql`

(be careful to use the 'evaluation' back-quotes: `...`) where script.sql looks something like:

        set feedback off

        set pagesize 0

        set linesize xxx

        set trimspool on

        ttitle off

        btitle off

        set verify off

        select col1, col2, ... colN

        from    ......

        where   ......

        exit

and the you can then read back values from ${m_var[i]}.

Simple applications will select one column from many rows, or many columns from one row, but there is no reason (beyond Occam and KISS) why you shouldn't pass out lots of columns from many rows, the results of several SQL statements..