Visit Our UNIX and Linux User Community


Looping through a shell script with sql statements


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Looping through a shell script with sql statements
# 1  
Old 10-07-2009
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
# 2  
Old 10-07-2009
A general solution for calling DB queries from a shell script:

Code:
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:

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

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

# 3  
Old 10-08-2009
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
# 4  
Old 10-08-2009
What is the value of the select_return variable? You also might add

Code:
set -x

to the beginning of the code to get some debug output.
# 5  
Old 10-08-2009
Thanks Pettero for your assistance

Previous Thread | Next Thread
Test Your Knowledge in Computers #293
Difficulty: Easy
Linux is the most popular operating system among supercomputers today (2019).
True or False?

10 More Discussions You Might Find Interesting

1. Programming

Shell script - if statements dont work

hi all, i have made a shell script and it runs until it reaches the if statement, doesn't the ! mean only if the command fails it will echo me that message and then exit can anyone please help me what is wrong with my code? many thanks, rob #!/bin/bash echo "is this archive... (10 Replies)
Discussion started by: robertkwild
10 Replies

2. Shell Programming and Scripting

Looping not completing in shell script

Hi, Iam using below code to login to servers to get cpu utilisation. but output is coming for only one server. code is below root@blr-svr-oclan-01 # more SSSC_CPU_UTIL1.sh #!/bin/sh echo "CPU UTILIZATION" while read line; do IDLE=`/usr/local/bin/sshpass -p 'xxx' ssh xxx@$line 'sar 2 2' |... (1 Reply)
Discussion started by: surender reddy
1 Replies

3. Shell Programming and Scripting

Nested looping statements

I cannot get the code below to work correctly. The IF statement works just fine, but not the looping. The inner loop tries to find files for a given vendor; if found, I need to sleep giving another process time to move the files. Once the given vendor's files are gone, then I want to move on to the... (1 Reply)
Discussion started by: dgreene
1 Replies

4. Shell Programming and Scripting

Can we use two shebang statements in a single shell script?

Hi, As per my understanding, we can use two shebang statements in a single shell script. Please see below snippet- #!/bin/bash .......## some code A #!/bin/csh .......## some code B exit 0; Here, code A will be executed using bash shell and code B will be executed with c shell. ... (9 Replies)
Discussion started by: tarunmudgal4u
9 Replies

5. Shell Programming and Scripting

looping some statements

Hi, assume there are some dir structure like - I need to write a script to create 5 new directories under 'qwe' dir of all the above 3 dir structures. these 5 dir will have same name. I don't want to use 15 mkdir statements. i just want to write 5 mkdir statemets and use them 3 times. I... (10 Replies)
Discussion started by: Sriranga
10 Replies

6. Solaris

Automate SQL statements

Hello, On unix side, I have written below script for taking oracle db backup. But when I tried to execute it - i could not execute the sql statements from unix. Please suggest ------------------------ $ more bkp.sh #!/bin/ksh # make sure database is shutdown cleanly sqlplus '/ as sysdba'... (3 Replies)
Discussion started by: panchpan
3 Replies

7. Shell Programming and Scripting

Calling SQL LDR and SQL plus scripts in a shell script

Hi- I am trying to achieve the following in a script so I can schedule it on a cron job. I am fairly new to the unix environment... I have written a shell script that reads a flat file and loads the data into an Oracle table (Table1) via SQLLDR. This Works fine. Then, I run a nested insert... (5 Replies)
Discussion started by: rajagavini
5 Replies

8. Shell Programming and Scripting

Convert shell script for looping

Situation: I have a working shell script on our file server (OSXS Tiger) to connect to a workstation, which is using a portable home directory (phd), and rsync a user's MirrorAgent.log. I'm not that strong of a scripter (obviously), but I would like to add other workstations to this script as they... (4 Replies)
Discussion started by: le0pard13
4 Replies

9. Shell Programming and Scripting

looping through a variable in a shell script

hi, my first question is :- i would like to know how do i loop through the output of a variable. for ex:- if i have a variable called x and echo $x gives the output like feb 19 07 feb 20 07 feb 21 07 i would like to know how do i loop through this since it is separated and i... (1 Reply)
Discussion started by: ramachandranrr
1 Replies

10. Shell Programming and Scripting

Running remote shell script containing sql statements

I have a shell script which resides on three SCO machines containing some simple sqlplus statments. I need to run these scripts remotely. Currently, I am trying to use rsh to do so: rsh hostname myscript args The problem is that the arguments to the sqlplus statements in the remote shell... (4 Replies)
Discussion started by: Madbreaks
4 Replies

Featured Tech Videos