Execute multiple SQL scripts from single SQL Plus connection...?

GoldenJet · #1 (**permalink**) 10-06-10, 09:47

I have found multiple examples and have coded my own shell script which will open a single SQL Plus connection and execute a single SQL script

e.g. sqlplus user/pwd @mysqlscript

Is there a way to open a SQL Plus connection in a shell script, keep it open, and pass multiple scripts to this connection before closing it?

e.g.

sqlplus user/pwd

cat script_list.txt | while read scriptname
do
echo executing $scriptname
@$scriptname
done

(yes, I know, but this illustrates idea of what I'm attempting to do)

The reason for this requirement is we will have multiple scripts to execute, but if one fails, then we need to rollback. If I open a new SQL Plus connection for every script (in a loop), when the SQL Plus connection exits, it does its wonderful 'commit on exit' and deprives us of a rollback. Plus, opening/closing SQL Plus can't be great overhead to have.

Are we going to have to construct one big SQL file from the individual scripts, create a save point at the beginning of the script, then execute and exit/rollback on error?

Any help/ideas are greatly appreciated.

kitaman · #2 (**permalink**) 10-06-10, 13:48

Create a 'requests' directory, that has text files in it, each text file containing the sql instructions.
Create 'done' for the completed jobs.

Code:

cat $$ >sql.pid
sqlplus user/pwd

while [ -r sql.pid ]
do
list=`ls requests`
for scriptlist in $list
do
cat $scriptlist |while read scriptname
do
echo executing $scriptname
@$scriptname
done
mv requests/$scriptlist done
done
sleep 20
done

To stop the process, delete sql.pid
Change the sleep line as appropriate.

GoldenJet · #3 (**permalink**) 10-08-10, 14:09

kitaman,

First, thank you for your reply and help. I am new to UNIX shell scripting so maybe I'm missing something obvious, but I'm having a bit of a problem getting your code example to work.

I created a test script based on the code provided in your reply. What's happening is SQL Plus is opening, but then nothing happens. Also, the 'requests' directory I created has 755 permissions, so I am not getting why 'cat' cannot open them...

Code

Code:

# !/bin/ksh
echo $$ >sql.pid
sqlplus userid/pwd


while [ -r sql.pid ]
do
   list=`ls r40_ac_test/reporter_backend/RPAPPL/Functions/`
   for scriptlist in $list
   do
      cat $scriptlist |while read scriptname
      do
        echo executing $scriptname
        @$scriptname
      done
      #mv requests/$scriptlist done
  done

sleep 5
done

STDOUT

Quote:

$ ./exe_sql.sh

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Oct 8 17:59:26 2010

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> exit (nothing happened for 30 seconds, so I exited SQL Plus)
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
cat: cannot open file1.sql
cat: cannot open file2.sql
cat: cannot open file1.sql
cat: cannot open file2.sql
cat: cannot open file1.sql
cat: cannot open file2.sql
cat: cannot open file1.sql
cat: cannot open file2.sql
cat: cannot open file1.sql
cat: cannot open file2.sql
cat: cannot open file1.sql
cat: cannot open file2.sql
cat: cannot open file1.sql
cat: cannot open file2.sql
cat: cannot open file1.sql
cat: cannot open file2.sql
cat: cannot open file1.sql
cat: cannot open file2.sql
cat: cannot open file1.sql
cat: cannot open file2.sql
cat: cannot open file1.sql
cat: cannot open file2.sql
cat: cannot open file1.sql
cat: cannot open file2.sql
cat: cannot open file1.sql
cat: cannot open file2.sql
^C (here I killed the proc)
$

kitaman · #4 (**permalink**) 10-08-10, 14:50

If you check the value of $scriptname, you will find that it is equal to 'file1.sql' etc.
The problem is that the script is not running in the directory where the files are located.
Create a variable at the beginning (before any of the loops):
DATADIR=r40_ac_test/reporter_backend/RAPPL/Functions
Then change the following:
list=`ls $DATADIR/`
and
cat $DATADIR/$scriptlist |while read scriptname
and later,
mv $DATADIR/$scriptlist done
Also, unless you move (mv) the files from the input directory, the script will continually reprocess them. Either move the files by un-commenting the mv statement, or temporarily add a line to rm the sql.pid file after the first loop.
Add
rm sql.pid
after the sleep statement

Internet Services

Web Development

Digital Marketing

Consumer Tech