Home » SQL & PL/SQL » SQL & PL/SQL » Function in shell
Function in shell [message #195021] Tue, 26 September 2006 11:21 Go to next message
uicmxz
Messages: 48
Registered: July 2006
Member
How I can check return value from PL/SQL function in shell script?

I have function in the package that returns status (1 - success, 2 - failed) of execute procedures which truncate table.
First I execute procedures in the shell script that truncate table. Then I call function to check status.
I would like to return status value from function to shell.

sqlplus ${pbmuser} <<EOF
EXEC BATCH_DDL.TRUNCATE_TABLE('RETR_TEMP');
EOF

sqlplus ${pbmuser} >> $logfile <<EOF

set serveroutput on
set feedback off

declare
status number;
begin
status := batch_ddl.get_exit_code'RETR_TEMP','TRUNCATE_TABLE' );
if ( status = 0 ) then
dbms_output.put_line( 'successful' );
else
dbms_output.put_line( 'faild' );
end if;
end;
/
EOF

Thanks
Re: Function in shell [message #195073 is a reply to message #195021] Tue, 26 September 2006 19:59 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://www.oracle.com/oramag/oracle/01-may/o31asktom.html
Re: Function in shell [message #195119 is a reply to message #195073] Wed, 27 September 2006 02:21 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or you can use the EXIT command which allows you to pass back a bind variable.
Try something like:
(untested)
set serveroutput on
set feedback off
variable e_stat number

begin
:e_status := batch_ddl.get_exit_code'RETR_TEMP','TRUNCATE_TABLE' );
if ( status = 0 ) then
dbms_output.put_line( 'successful' );
else
dbms_output.put_line( 'faild' );
end if;
end;
/

EXIT :e_stat
Previous Topic: Coonecting to DB on Forms 6i
Next Topic: Need data after a transaction
Goto Forum:
  


Current Time: Thu Dec 08 03:55:43 CST 2016

Total time taken to generate the page: 0.23150 seconds