Shell Programming and Scripting

Hello friends,

This query is with regards to a script (pl/sql) which returns multiple values.

Please see below script wherein the query returns a single value and is assigned to a single variable

Code:

DB_VALID_CDR=`sqlplus -s user/pass<<!EOF | grep -v "^Connected"  2>&1
set termout off echo off feedback off pause off timing off time off
set pages 0
clear breaks
clear compute
set termout on
select a.VALUE_INTEGER from client_statistics a, statistic_type b where a.STATISTICS_TYPE=b.Statistic_type_id and a.STATISTICS
_OBJECT like '%$VAR%' and b.STATISTIC_TYPE='valid_cdrs';
EXIT
!EOF`

Now if my sql query is modified to return mutliple values (columns, see below code) how can I get those values assigned in a variable/variables?

Code:

select STATISTICS_OBJECT, VALUE_INTEGER , START_PERIOD,END_PERIOD 
from client_statistics 
where 
STATISTICS_TYPE=385 
and to_char(sysdate,'dd/mm/yyyy HH24')=to_char(end_period,'dd/mm/yyyy HH24')
and VALUE_INTEGER > 0
and rownum=1

Finally this sql code will be embedded in a shell script

Thanks for your help.

Hello... First off I do not understand to what variable you are assigning the result in your script?

I can tell you that INSIDE sql (in a pl/sql procedure or sql script) you assign to a variable (assuming you will get only a single row from your select) with:

SELECT a.COLx into VARy from TABLEz a, where.... etc. If the variable comes from outside then it must be a "bind variable" and would be expressed as :VARy I believe... For multiple columns (still one row) you would do:

SELECT a.COLb, a.COLc, a.COLd INTO VARx, VARy, VARz from TABLE... etc.

But you have a big problem if more than ONE row is returned from your query because you get a TOO MANY ROWS error. If you will return more than one row you use BULK COLLECT as in:

SELECT a.COLb, a.COLc, BULK COLLECT INTO VARx, VARy from TABLE... This gives now an array (in PL/SQL terms, a list) for VARx[], VARy[], etc. and there are functions for looping through these bulk collected arrays.

I know this all works inside a PL/SQL procedure and also in a sql script, but I don't know about the way you are doing it as a "here document" in a shell script.

Hope this helps though...

Thanks Quine...I got the result in a variable and then tried "cut -d" on it