Shell Script: Traverse Database Table Row by Row

UNIX for Beginners Q & A UNIX for Dummies Questions & Answers
1

Hello Everyone,
My issue is that I want to traverse a database table row by row and do some action on the value retrieved in each row.

I have gone through a lot of shell script questions/posts. I could find row by row traversal of a file but not a database table.

Please help.

Thanks & Regards,
Ahsan Ashgar

2

which db ?

how you are connecting to the db. post the code

4

The database table is on Oracle DB.

Please see the code below for connecting to DB:

MIN_BATCH=`sqlplus -s user/pw@sid<<eof
	set serveroutput on;
	set feedback off;
	set linesize 1000;
	SELECT to_char(Min(IF_ROW_BATCH_NUM),'999999999999999')MIN FROM SIEBEL.EIM_SRV_REQ WHERE IF_ROW_STAT = 'FOR RECUR IMPORT';
	`
	min="${MIN_BATCH#"${MIN_BATCH%%[[:digit:]]*}"}"   # strip off non-digit prefix from str ...........................................................................

=================================
Now I have to pick multiple values/rows rather than just the minimum batch number. So I need to traverse the table row by row.

Regards,
Ahsan

5

Have a look at this example of reading data into shell variables from a SQL/Plus session. It should be of some help:

6

How about something like:

sqlplus -s user/pw@sid > /tmp/$$ <<eof 
	set serveroutput on;
	set feedback off;
	set linesize 1000;
	SELECT * FROM table;
EOF

while read LINE
do
       ...
done < /tmp/$$

rm -f /tmp/$$

Better to use a temp file than to cram it in backticks because if the output is large enough, backticks will truncate it.

It'd be more easily possible to pipe it into the while loop instead of storing in a temp file if you used a sql script:

sqlplus -s user/pw@sid < script.sql | while read LINE
do
        ...
done
7

you'll most likely need:

set pagesize 0;
set heading off;

too.