How to execute an Oracle procedure using shell

Shell Programming and Scripting
1

Hi ,

i have created an .sh file that has the following code:

#!/bin/ksh
sqlplus -s p1istuat/p1istuat@CWS_IST6 @Procedure_Execute.sql &
sqlplus -s p1istuat/p1istuat@CWS_IST6 << EOF
exit
EOF

The mentioned Procedure_Execute.sql file inside has the following code:

exec TEST;

i tried to run the shell file as "sh filename.sh" its giving me as sqlplus cannot be found...

please let me know any solution for this....

2

chk whethere oracle is installed in ur unix machine ....

if installled check the path
to check the location of installation
try which command

/home/mkdmt/src/sh: which sqlplus
/opt/dbms01/app/oracle/product/10.2.0.2/bin/sqlplus

3

Hi ,

Thanks a lot .I have checked for that.Its giving me :

Unknown user: optier

Also i have done saying echo $ ORACLE_HOME seems there is no oracle installed in that machine.

i have changed the entire .sh file to the following :

part_info=`sqlplus p1istuat/p1uatist@CWS_IST6 <<_EOF_
set serveroutput on size 10000
declare
abc integer;
begin
TEST;
dbms_output.put_line('return code is:') ;
end;
/
exit;
_EOF_`

i got the above code from some forum.
In this case how to see the output means how can i view 'part_info'

4

:confused: no reply??????

5

It's not allowed to bump up questions, please read our rules.

6

Hi vins,

You are already got the above 90% stuff for executing the Stored Procedure from Shell Script. Why you are repeating the question ?????

RETVAL=`sqlplus -s USERNAME/PASSWORD@DBNAME <<EOF
SET SERVEROUTPUT ON SIZE 100000
Declare
OUT_STATUS NUMBER;
OUT_MSG VARCHAR2(200);
Begin
ODS_SP_REMOVE_PRE_SUB_DUP(OUT_STATUS, OUT_MSG);
dbms_output.put_line ('KeepThis '||OUT_STATUS ||' '||nvl(OUT_MSG,''));
End;
/
SET SERVEROUTPUT OFF
EXIT;
EOF`

X=`echo $RETVAL | grep KeepThis | awk '{print $2}'`
Y=`echo $RETVAL | grep KeepThis | awk '{print $3}'`

echo "Procedure: ODS_SP_REMOVE_PRE_SUB_DUP output is: "
echo "OUT_STATUS= $X"
echo "OUT_MSG= $Y "

Note: Set the Environmentol variables ($ORACLE_HOME, $PATH, $ORACLE_BASE, $ORACLE_SID, $TNS_ADMIN)are must before call the Oracle Stored Procedure.