Shell Programming and Scripting

Hi,

I am new to shell scripting, I am trying to write a shell script, which will automate the process of mailing the invoices at the end of the day. For major part I am using Oracle database function.
The problem is I want to capture the value returned by the Oracle function into the script variable. In fact this value will be a command which when exceuted will send the mail.
This is a part of the script:
#---------------
#/usr/lib/sendmail $tomail < $tmpfile
ERR=$?
if [ $ERR = 0 ]; then
sqlplus -s username/password << EOF
DECLARE
l_error varchar2(100);
l_command varchar2(1000);
l_inv number;
BEGIN
my_oracle_package.invoice_proc(NULL,NULL,400,l_inv,l_error,l_command);
END;
/
exit 0
# Here I would like to capture l_command into some variable.
EOF
#-------------
Now I want to capture the value l_command(Character 1000) returned by Oracle procedure. Is this possible, if yes , how?

Thanks
Prasad.

Hi Phrasad,

It is possible to get the exit code of each forked process. It's up to the process to use a valid exit code. For instance SQL could deside to exit with the code 3489, I would not know what to do with this code, but SQL can do this.
So when you start SQL you fork, when this fork returns to it's parent, "the exit from out of sql" you will see the return code from SQL. The fork cannot tell exit codes from inside another programm. Maybe from inside SQL you are able to capture the exit code and use this to exit out of SQL.
Hope this helps.

Regs David

The only way I know of doing this is by extracting your data from your db to a flat file, then setting a variable by using cat and piping to a cut command to extract your result from your sql....