Looping through a shell script with sql statements

[novice82]

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

[Rubberman]

First of all, you can do this (the SQL commands) in an SQL script (sqlplus), not a system shell script (bash, csh, ksh, etc). I would suggest that you first review the documentation for sqlplus.