Shell script with PL/SQL procedure?

[Unix or Linux?]

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

[Calandrello]

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

[Patrick Wallek]

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 · Accepted solution]

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?]

Thanks Spex, seems to work