Looping through a shell script with sql statements

novice82 · 10-07-2009, 10:56 AM

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

Code:

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

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

Code:

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:

Code:

select count(*) from snapshot1

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

Code:

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,

Code:

echo " select max(value) from results_all "

-- printing the max value to console
5. Process my files using the following jobs:

Code:

./runscript.ksh - READ -i $m

( m - initial value 001 )

Code:

./runscript.ksh - WRITE -i $m

( m - initial value 001 -- same as READ process_id )
-- increment m by 1
6. Wait for success log

Code:

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.

Code:

done

-- End of Step 1

Pointers on getting me moving will be very valuable.

thanks,

Kristina

Doculus · 10-07-2009, 12:40 PM

Hi Kristina,

you could use the Oracle's sqlplus command to send the SQL commands to the server.
Check your documentation for detailed arguments of the command, I currently do not use it.
You could write out the SQL commands into a temporary files in the script, and execute it with the '@tmpfile.sql' argument.
As I remember sqlplus does not supports giving the command in the command line, unfortunatelly.
Also, you should set some options for accessing the database, like database, username/password.
Hope it helps!

catkin · 10-07-2009, 12:49 PM

Quote:

Originally Posted by Doculus

As I remember sqlplus does not supports giving the command in the command line, unfortunatelly.

But it may accept input from a here document. Scroll down to 3.6.6 Here Documents.

chrism01 · 10-07-2009, 06:16 PM

Quote:

You can also run a file at connection by using a special form on the Unix command line. The form of the command is:

sqlplus <yourName>/<yourPassword> @<fileName>

http://infolab.stanford.edu/~ullman/...ql+from+a+file

novice82 · 10-08-2009, 12:16 AM

hello all,

thanks for the inputs.

How do you suggest I handle step 3 and 4 of my question ?

I would imagine i need to store it into a variable and match it using a shell command line ?

Some syntax for that will be helpful..

thanks.

novice82 · 10-08-2009, 12:24 AM

I tried the following, but get a permission denied error!

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

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

Error is : Select_return: execute permission denied

regards

kris

chrism01 · 10-08-2009, 01:29 AM

Extending my prev post

Code:

echo "select count(1) from snapshot1" >file.sql
result=`sqlplus <yourName>/<yourPassword> @file.sql`

Possibly even

Code:

result=`sqlplus <yourName>/<yourPassword> 'select count(1) from snapshot1'`

Unfortunately I don't have an Oracle instance to play with.
Note that count(1) is faster than count(*); count(1) counts once (actually the scalar value 1) for each row, count(*) counts through all the col names for every row.
Also avoids possibility of shell interpreting '*' if you put it in double quotes "*".

Other variations http://www.unix.com/shell-programmin...scripting.html

novice82 · 10-08-2009, 09:02 PM

Thanks a lot mate. That was very helpful!

cheers

kris