JL Computer Consultancy

Passing arrays of values from SQL*Plus to the shell.

August 1999

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   ......

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..

Back to Main Index of Topics