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