Calling a plsql procedure from UNIX shell script
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Calling a plsql procedure from UNIX shell script

  1. #1
    Join Date
    Jun 2012
    Posts
    1

    Calling a plsql procedure from UNIX shell script

    Hi,

    I have a plsql procedure, which raises an exeption on some condition or simply exits. Now if my procedure raises exeption, then i want to use this error to perform some operation in unix shell script.

    I want something like this

    #unix script starts
    #connect to sql
    #call a procedure
    #exit from sql
    #if (procedure raises an error) then
    # ACTION else
    # exit....

    I think my question is clear to you all.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    You can redirect output from sqplus to a variable. Just be aware that 9i ignores the -s switch.
    You can then check the variable for whatever it is you are looking for.

    PLSQL_RESULTS=`sqlplus -s '/as sysdba' < set term off
    set echo off
    set pagesize 0
    set feedback off
    set timing off
    set serveroutput on
    set term on
    exec runMyProc;
    EOF

    Here is a simple SQL example.

    DB_STATUS=`sqlplus -s '/as sysdba' < set term off
    set echo off
    set pagesize 0
    set feedback off
    set timing off
    set term on
    select status from v\$instance;
    EOF

    if [ "${DB_STATUS}" == OPEN ]
    then
    echo "The database is open"
    else
    echo "The database is not open"
    fi

    this space intentionally left blank

Tags for this Thread

Posting Permissions

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


Click Here to Expand Forum to Full Width