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