#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    3
    Rep Power
    0

    Question Running sql quiries from UNIX shell scripts.


    Hi All,
    I am in a problem, I want to run sqlplus queries through UNIX shell scripts. But the moment I execute the script file it exits from the sql plus prompt. What is the way of executing sql queries fromk within a script file in UNIX box.


    Arnab Panja
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    60
    Rep Power
    11
    I'm not sure how you're doing it, but here's my normal method (using a "here document"):

    Code:
    sqlplus <<ENDOFSQL
    connect <username>/<password>
    select * from dual;
    exit
    ENDOFSQL
    Note that it's very important that there aren't any spaces between the end of ENDOFSQL and the end of the line.

    It's also best to pass the username and password from within the here doc so that they don't show up on in a "ps" command. This would happen if you used the following code:
    Code:
    sqlplus <username>/<password> <<ENDOFSQL 
    select * from dual;
    exit
    ENDOFSQL
    You can use any text string as the tag not just ENDOFSQL. You'll find the most common is a !, although it helps makes your code more readable if you use something meaningful.

    You might find my response in the thread "truncate table script" by svvc useful too:
    Hi Sachin,

    I find it simplest to use a "here document" within a UNIX script to execute SQL commands.

    I've attached an example below that is using external o/s authentication (the whole passing usernames and passwords to Oracle via a script is a bit messy if you don't want the password hardcoded in the script, or showing up on the ps listing).

    Note that the $ is a special character in the UNIX shell so if you use it in a here document you need to escape it with a "\" (see how I've had to run "select * from v$instance;" below).

    Good luck,
    Andy

    Code:
    #!/usr/bin/ksh
    
    #-- Get the table to be truncated
    TABLE_NAME=$1
    
    #-- Exit if the table_name has not been supplied
    if [ -z "${TABLE_NAME}" ]; then 
      echo "ERROR: table_name not supplied"
      exit 10
    fi
    
    #-- Execute the SQL
    sqlplus / <<+ENDOFSQL+
    select * from v\$instance;
    truncate table ${TABLE_NAME};
    exit
    +ENDOFSQL+
    
    exit 0
    Cheers, Andy
    Last edited by andyb1ack; September 27th, 2004 at 05:02 AM. Reason: Added response from "truncate table thread"
  4. #3
  5. No Profile Picture
    ......@.........
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2004
    Posts
    1,345
    Rep Power
    56
    Here's a pretty robust solution becaus4e it allows you to set up special session settings - the SET TRIMSPOOL OFF - for example and it blocks some sqlplus chatter for you:
    Code:
    exsql ()  {
     awk '{ if(NR == 1) {
            STR=sprintf("%s/%s",UI,PW)
            print STR
            print "set pagesize 0;"
            print "set linesize 2000;"
            print "set MAXDATA 60000;"
            print "set heading off;"
            print "set trimspool off;"
            print "set echo off;"
            }
            print $0}' UI=$UID PW=$PSWD | sqlplus -s  | \
            sed '
                /^$/d
                /^Connected.$/d
                s/ *|/|/g
                s/| */|/g
                s/^ *//g
                s/ *$//g
                /rows selected/d
                /Session altered/d
            '
    }
    Use exsql like this:
    Code:
    echo "
    select  *
    from    ucrdpst
    where   ucrdpst_cust_code = $cust and
            ucrdpst_prem_code = '$prem'
            ;"|exsql > outfile.txt
    This allows you to use unix shell variables in the query and save the results of the query in outfile.txt
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    3
    Rep Power
    0
    Originally Posted by Arnab Panja
    Hi All,
    I am in a problem, I want to run sqlplus queries through UNIX shell scripts. But the moment I execute the script file it exits from the sql plus prompt. What is the way of executing sql queries fromk within a script file in UNIX box.


    Arnab Panja
    Yes, the here document worked. I can execute all queries from within a shell script.
    Thanks folks.

    Bye
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    3
    Rep Power
    0
    Originally Posted by Arnab Panja
    Hi All,
    I am in a problem, I want to run sqlplus queries through UNIX shell scripts. But the moment I execute the script file it exits from the sql plus prompt. What is the way of executing sql queries fromk within a script file in UNIX box.


    Arnab Panja
    Yes, the here document worked. I can execute all queries from within a shell script.
    Thanks folks.

    Bye
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2007
    Posts
    1
    Rep Power
    0

    Angry while querying the database i am getting error


    exsql () {
    awk '{ if(NR == 1) {
    STR=sprintf("%s/%s",sa,sa@iccsdev_clone)
    print STR
    print "set pagesize 0;"
    print "set linesize 2000;"
    print "set MAXDATA 60000;"
    print "set heading off;"
    print "set trimspool off;"
    print "set echo off;"
    }
    print $0}' sa=$UID sa=$PSWD | sqlplus -s | \
    sed '
    /^$/d
    /^Connected.$/d
    s/ *|/|/g
    s/| */|/g
    s/^ *//g
    s/ *$//g
    /rows selected/d
    /Session altered/d
    '
    }
    echo "
    select * from table_time_bomb"|exsql > outfile.txt


    while executing above code i am getting error .

    awk,
    STR can not be parse correctly


    plz give me suggestion. whats wrong in this code.

IMN logo majestic logo threadwatch logo seochat tools logo