Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Posts
    2

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

    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.

  2. #2
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    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.
    Last edited by kitaman; 10-06-10 at 14:49. Reason: typos

  3. #3
    Join Date
    Oct 2010
    Posts
    2
    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
    $ ./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)
    $

  4. #4
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    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
    Last edited by kitaman; 10-08-10 at 16:00.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •