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;