Home » Infrastructure » Unix » Invoking Oracle stored procedure in unix shell script (Oracle 10g)
Invoking Oracle stored procedure in unix shell script [message #301080] Tue, 19 February 2008 02:54 Go to next message
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 #301088 is a reply to message #301080] Tue, 19 February 2008 03:16 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Use CODE in uppercase in your tags (and remove the / in the first one Wink )

Now for your question: there is no immediate way to "export" the values of status_code and status_code to the calling shell-script.

What you could do is to display the values within your SQL-part, redirect the output of SQL*plus to a temporary-file and grep the values out of them.
In other words: a bit of shell-script-hacking Wink

Re: Invoking Oracle stored procedure in unix shell script [message #301101 is a reply to message #301088] Tue, 19 February 2008 03:38 Go to previous message
ThomasG
Messages: 3211
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.
Previous Topic: PL/SQL procedure that reads from a directory
Next Topic: shell script to check the lsitener
Goto Forum:
  


Current Time: Fri Mar 29 09:16:14 CDT 2024