| Invoking Oracle stored procedure in unix shell script [message #301080] |
Tue, 19 February 2008 02:54  |
hidnana
Messages: 87
Registered: November 2007
Location: chennai
|
Member |
|
|
Here's a shell script snippet.....
[/code]
cd $ORACLE_HOME/bin
Retval=`sqlplus -s <<eof
$TPDB_USER/april@$TPD_DBCONN
whenever SQLERROR exit 2 rollback
whenever OSERROR exit 3 rollback
set serveroutput on
set pages 999
var status_desc char(200)
var status_code number
exec p_tpdb_mv_refresh('$1', :status_code, :status_desc);
eof`
echo $Retval
[/code]
:status_code, :status_desc are declared as output variables in the PL/SQL procedure where they're assigned values. How to retreive the values they store in the shell script ?
|
|
|
|
|
|
| Re: Invoking Oracle stored procedure in unix shell script [message #301101 is a reply to message #301088] |
Tue, 19 February 2008 03:38  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
You might in the long run also consider not digging deeper into the shell script hole and switching to Perl on she shell level, where you can work with functions and procedures and general SQL more easily.
Plus you are able to catch errors on the SQL level, whereas in the shell script/sqlplus backtick strategy errors in the SQL go unnoticed and just show up as garbled data in some variable.
Plus, you can prepare statements and execute them repeatedly with bind parameters, which speeds up execution.
Also, since Perl is available on almost any platform, ( including and implementation for Windows from ActiveState) you also gain some platform independence)
Have a look at the last two examples here for calling procedures with in/out parameters.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
)