Calling a plsql procedure from UNIX shell script

[gdgujrati]

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.

[gandolf989]

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