topic Re: Shell script with PL/SQL procedure? in Operating System - Linux

Shell script with PL/SQL procedure?

Unix or Linux? — Fri, 13 Oct 2006 08:56:59 GMT

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

Re: Shell script with PL/SQL procedure?

Calandrello — Fri, 13 Oct 2006 09:06:14 GMT

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

Re: Shell script with PL/SQL procedure?

Patrick Wallek — Fri, 13 Oct 2006 09:29:33 GMT

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

Re: Shell script with PL/SQL procedure?

spex — Fri, 13 Oct 2006 09:49:10 GMT

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

Re: Shell script with PL/SQL procedure?

Unix or Linux? — Fri, 13 Oct 2006 10:38:30 GMT

Thanks Spex, seems to work