Problems with fetching data from Oracle database

Shell Programming and Scripting
1

Here's a database query which looks up the NAME column of PRODUCT table

SELECT NAME FROM PRODUCT ;

And this query retrieves me the following output

SUGAR
COCOA
HONEY
WHEAT
CABBAGE
CAULI FLOWER
[6 rows selected]

Please note the last record CAULI FLOWER contains TWO blank spaces between the two words.

Now I want to do the same using Unix script and I wrote the following :

set -A prod_list `sqlplus -s USER/PWD@DB <<!
SET HEADING OFF ;
SET NEWPAGE NONE ;
SET FEEDBACK OFF ;
SET LINESIZE 4000 ;
SET WRAP OFF ;
SELECT NAME FROM PRODUCT ;
!
`

and then queried the prod_list array to find 7 instead of 6 entries

for product in ${prod_list[@]} ; do
echo $product
done

The output is like

SUGAR
COCOA
HONEY
WHEAT
CABBAGE
CAULI 
FLOWER

7 items in the array. :eek:
My guess is the TWO interim spaces within the last record value is splitting the record in two seperate records
at that point of time when I am trying to fetch the records via sqlplus (ie., via Unix).
Can this behavior be overridden ? If yes how ?
Also we tried to use a SQL cursor to fetch the values into unix , but we do not know exactly how to do it. :frowning:
Can anybody help us on that too ?

Please note :
Unix version : Korn Shell
OS : Linux
Database : Oracle 11g

Thanks
Kumarjit.

2

Could I suggest a variation to your query:-

SELECT ''''||NAME||'''' FROM PRODUCT ;

There are four single quotes at the beginning and end of the field you are requesting along with double pipe marks to concatenate. This should return you quoted values that I hope will load your array.

I regret I don't have anything I can test this on though, so can you give it a try and let us all know how you get on.

I hope that this helps,

Robin
Liverpool/Blackburn
UK

3

I would suggest instead of using an array, spool the result to a file.

spool prod_list.txt
SELECT NAME FROM PRODUCT ;
spool off;

Then later use a while loop to read entries from the spooled file.

while read product
do
  echo "$product"
done < prod_list.txt
4

Its a mixed experience , as the solution from rbatte1 wasnt fruitful enough but bipinajith 's clicked good time.
And for that I would like to thank you both for chipping in with your feedbacks , irrespective of the yield. :b:

I am just stuck at some other place that i might seek your help to pass by.

The SPOOL command

spool prod_list.txt
SELECT NAME FROM PRODUCT ;
spool off;

not only writes all the data outcome into the file but also displays the output in the Unix command line , and I must not show to the user any output from the database.

How to go for it ?????

1 Like
5

To suppress the output to stdout, try this:

Make sure you are connecting using silent mode -s , also SET TERM OFF & use a variable:

SqlOut=`sqlplus -s USER/PWD@DB <<!
SET TERM OFF

---------- Post updated at 10:10 ---------- Previous update was at 09:04 ----------

if above methods does not help, then redirect the output to /dev/null

sqlplus -s USER/PWD@DB <<! > /dev/null