Returning result of an Oracle stored procedure to a shell script

[Jeanine Kone]

We have a data load here that is basically a shell script that calls a couple of sql scripts, which in turn, call a few stored procedures. We are trying to add some error processing to the process.

What I would like to do is be able to return a value from the stored procedure back to the shell script. I could then use that value to control the rest of the program execution (i.e fire off an error e-mail, or keep going with the processing). I am thinking that I should be able to do this, but just don't know quite how...

I am limited to regular shell script commands -no perl, c, etc...

Any suggestions?

[RAC_1]

you can get the status of executed command in your script. But I think you will get the error code for your sql scripts.(In this case you can put it to seperate file and tehn read it)

echo $? display the return status of the last excuted command. 0 means OK and 1 means NOTOK

[Jeanine Kone]

Anil,

Thanks for the quick response. As far as the return status of the last command - that won't work b/c the comamnd is the call to sqlplus - which will be sucsessful either way.

You also mention writing the status to a separate file. I have considered this one - my problem is that yu can't just spool from a stored procedure. We have not used the built-in utlfile procedures yet. Is there an easy way to "write out" this status from within the stored procedure?

[A. Clay Stephenson]

Your fundamental problem is that you can't simply use the exit status of Sqlplus because that is almost always going to be zero. This would be my attempt at your problem.

1) Use the PID of your current shell to build a
known temp filename. e.g. /var/tmp/X${$}_1.out

2) Send that filename in to a second script that builds SQL scripts 'on the fly' and instructs SQL to list results in the temp file. e.g. Line1 might contain the results of the first stored procedure. You then call sqlplus from this shell script.

3) You should then be able to read the temp file in your parent shell. When finished, remove the temp file.

[Jeanine Kone]

A. Clay - Thanks. Just to make sure I am understanding. With this suggestion, I would basically spool to this filename in the sql script and then use dbms_output (or something similar) to write messages to the output file. Which I can then read the results from.

This is pretty much the only option I could think of (well, not the whole dynamic filename thing and all, but the basic concept). I can go that way, but, before I do - just to verify - is there really no way to get an actual returned value from the stored procedure?

Thanks!

[Brian Crabtree]

There isn't any way to pass a variable back to a shell script. The only option I could think of would be using dbms_output with a spool file, or possibly a utl_file write to an external file with the code for the system.

I have personally been finding that doing major coding portions using dbms_job is faster and easier, and keeps you from using shell scripts where possible.

If you would like some help outside of the forum, write back and I may be able to give you some ideas.

Brian

[Simon Clarke_1]

Hi, Try this.

Script:
#!/bin/sh
VALUE=`sqlplus -silent scott/tiger@pf7a <set pagesize 0 feedback off verify off heading off echo off
select count(1) from user_objects;
exit;
END`
if [ -z "$VALUE" ]; then
echo "No rows returned from database"
exit 0
else
echo $VALUE
fi


Output :
v701-DEV(sjc)$ t
443

This came from metalink. Search on Subject: Passing values to OS shell scripts.

HTH
Simon

[Jeanine Kone]

Brian - Thanks. Those were the only options I could comeup with too. Is dbms_job part of OEM? I might take you up on you offer for ideas. I am off today - but I'll try to contact you tomorrow.

Simon - I'll try your suggestion out - I might be able to get that to do what I need instead of actually passing a value back.

I'll assign points tomorrow after I try them out.

[Volker Borowski]

Hi,

well the easiest way inside a database to "log" is to log to a table.

You could either log by direct call or can do it by using a trigger.

After a crash you just select the logtable and see how far it got.
Volker

[Mark Seaman]

You can easily pass an exit value from a SQL*Plus script back to a shell script.

First, use the WHENEVER OSERROR/SQLERRROR comands to set the return code of the SQL*Plus script to a value you can trap:

WHENEVER OSERROR EXIT 2;
WHENEVER SQLERROR EXIT 3;

Then use a bind variable in SQL*Plus that you can set from inside a PL/SQL procedure:

VARIABLE return_val number;

Inside your procedure, you set this value by prepending a colon:

IF ts_count != 0 THEN
:return_val := 2;
END IF;

Finally, as the last line of your script, use the SQL*Plus exit command, returning the bind variable as the exit status.

exit return_val;

[Dave Burton_1 · Accepted solution]

To pass an output parameter from a stored proc directly to a shell script variable, you can use the following technique.

#!/bin/sh

SHELL_VAR=`sqlplus -s un/pw <set feedback off pages 0
var plusvar varchar2(100)
exec stored_proc_name (:plusvar)
print plusvar
EOF`

echo ${SHELL_VAR}
# or process SHELL_VAR according to your needs

This way, you won't have to rework your stored proc to include dbms_output statements, you can use it just as it is. Also, you can return character values - SQL*Plus exit restricts you to numeric values only.

[Jeanine Kone]

Thanks Everyone - I got what I needed.

Simon - yours did work, and got me part way there. It would have been an okay workaround if I needed to.

Mark - yours also would have worked if I could not get exactly what I wanted (returning different text statuses).

Dave - Exactly what I needed.

For future reference, here is what I ended up with:

the shell script:

VALUE=`$ORACLE_HOME/bin/sqlplus -s user/pass @sqlscript`

if [ "$VALUE" = 'ERROR TYPE 1' ];
then
code to process here;
else
code to process here;
fi

the sql script:

set pagesize 0
set feedback off
set verify off
set heading off
set echo off

var out_status varchar2(30)
exec sp_tester(:out_status);
print out_status
exit;

the stored procedure:

procedure sp_tester (out_status out varchar2) is
begin
select 'ERROR TYPE 1' from dual;
end;

Thanks again everyone.