Looping through a shell script with sql statements

Hello members,

I'm working on the Solaris environment and the DB i'm using is Oracle 10g.

Skeleton of what I'm attempting;

Write a ksh script to perform the following. I have no idea how to include my sql query within a shell script and loop through the statements. Have therefore given a jist of what I'm attempting, below.

1. Copy file to be processed (one file at a time, from a list of 10 files in the folder ).

for i in *
do
  cp $i /home/temp

2 . Create a snapshot(n) table : Initialize n = 1

create table test insert account_no, balance from records_all;

-- creates my snapshot table and inserts records in SQL

3. Checking if the table has been created successfully:

select count(*) from snapshot1

-- query out the number of records in the table -- always fixed, say at 400000

if( select count(*) from snapshot(n) = 400000 )
  echo " table creation successful.. proceed to the next step "
else
  echo " problem creating table, exiting the script .. "

4. If table creation is successful,

echo " select max(value) from results_all " 

-- printing the max value to console

5. Process my files using the following jobs:

./runscript.ksh - READ -i $m

( m - initial value 001 )

./runscript.ksh - WRITE -i $m

( m - initial value 001 -- same as READ process_id )

-- increment m by 1

6. Wait for success log

tail -f log($m)* | -egrep "^SUCCESS"

7. looping to step1 to :
   Copy file 2 to temp folder;
   create snapshot(n+1) table

Exit when all the files have been copied for processing.

done 

-- End of Step 1

Pointers on getting me moving will be very valuable.

thanks,

Kristina

A general solution for calling DB queries from a shell script:

SQL="select something from somewhere where some_constraints"
sqlplus username/password <<EOF
$SQL;
EOF

You could probably assign the full output of the select to a variable and then make your comparison to validate table creation possibly like this. I don't have an Oracle DB to play with and the syntax might not be quite right but the idea should be reasonable:

SQL="select count(*) from snapshot1"
select_return=`sqlplus username/password <<EOF
$SQL;
EOF`

if [ $select_return -eq 400000 ]
then
  echo do something
fi

Thanks Peterro.

I get a "unknown operator" error when i execute the if statement. Appreciate if you can give me the right systax

thanks

Kris

What is the value of the select_return variable? You also might add

set -x

to the beginning of the code to get some debug output.

Thanks Pettero for your assistance