Languages and Scripting

Re: Shell script with PL/SQL procedure?

 
SOLVED
Go to solution
Unix or Linux?
Frequent Advisor

Shell script with PL/SQL procedure?

Is it possible to have a pl/sql procedure called from within a shell script. Seems it is not working for me.

ORACLE_SID="EM"; export ORACLE_SID
sqlplus user1/user1 << EOF
begin
procedure1;
end;
EOF
4 REPLIES 4
Calandrello
Trusted Contributor

Re: Shell script with PL/SQL procedure?

Friend to inside call one shell sql it is enough to enter in sql and to type @shellname
Example:
sqlplus /nolog @shellname
Patrick Wallek
Honored Contributor

Re: Shell script with PL/SQL procedure?

This should work:

ORACLE_SID="EM"; export ORACLE_SID
sqlplus user1/user1 << EOF
@procedure1
exit
EOF

This assumes the procedure1 is procedure1.sql and resides your current directory.

You can also do:

ORACLE_SID="EM"; export ORACLE_SID
sqlplus user1/user1 << EOF
@/path/to/procedure1.sql
exit
EOF
spex
Honored Contributor
Solution

Re: Shell script with PL/SQL procedure?

Hi,

Make sure the 'sqlplus' binary is in your path. To call a procedure in PL/SQL, use 'exec':

#!/usr/bin/sh
export ORACLE_SID=EM
export ORACLE_HOME=/foo/bar
export ORACLE_BIN=${ORACLE_HOME}/bin
export PATH=${PATH}:${ORACLE_BIN}

sqlplus user/pw << _EOF_
exec procedure1;
exit;
_EOF_

exit

PCS
Unix or Linux?
Frequent Advisor

Re: Shell script with PL/SQL procedure?

Thanks Spex, seems to work