LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 10-07-2009, 10:56 AM   #1
novice82
LQ Newbie
 
Registered: Oct 2009
Posts: 13

Rep: Reputation: 0
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 ).
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
 
Old 10-07-2009, 12:40 PM   #2
Doculus
LQ Newbie
 
Registered: Oct 2009
Distribution: Gentoo
Posts: 29

Rep: Reputation: 17
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!
 
Old 10-07-2009, 12:49 PM   #3
catkin
LQ 5k Club
 
Registered: Dec 2008
Location: Tamil Nadu, India
Distribution: Debian
Posts: 8,576
Blog Entries: 31

Rep: Reputation: 1195Reputation: 1195Reputation: 1195Reputation: 1195Reputation: 1195Reputation: 1195Reputation: 1195Reputation: 1195Reputation: 1195
Quote:
Originally Posted by Doculus View Post
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.
 
Old 10-07-2009, 06:16 PM   #4
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.9, Centos 7.3
Posts: 17,357

Rep: Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367
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
 
Old 10-08-2009, 12:16 AM   #5
novice82
LQ Newbie
 
Registered: Oct 2009
Posts: 13

Original Poster
Rep: Reputation: 0
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.
 
Old 10-08-2009, 12:24 AM   #6
novice82
LQ Newbie
 
Registered: Oct 2009
Posts: 13

Original Poster
Rep: Reputation: 0
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
 
Old 10-08-2009, 01:29 AM   #7
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.9, Centos 7.3
Posts: 17,357

Rep: Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367Reputation: 2367
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
 
Old 10-08-2009, 09:02 PM   #8
novice82
LQ Newbie
 
Registered: Oct 2009
Posts: 13

Original Poster
Rep: Reputation: 0
Thanks a lot mate. That was very helpful!

cheers

kris

Last edited by novice82; 10-08-2009 at 09:03 PM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with shell script looping... ech310n Programming 1 10-19-2008 03:06 PM
shell script having multiple grep statements-I want input file to be read only once mukta9003 Linux - Newbie 4 08-27-2008 12:58 AM
SQL: multiple WHERE statements elvijs Programming 2 07-02-2007 09:17 AM
shell script looping ust Linux - General 2 01-19-2005 04:05 AM
if statements and case statements not working in bourne shell script mparkhurs Programming 3 06-12-2004 02:41 AM


All times are GMT -5. The time now is 09:36 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration