How to know in a unix script if oracle procedure executed or failed?

[vishal]

Hi,

I am writing my first script to execute a Oracle procedure through a
unix script. Could you tell me how do I do error handling here? How do
I know in my script if the procedure executed successfully or failed?
Thanks

#!/usr/bin/ksh
sqlplus user/password@name <<EOC
set time on
set echo on
select sysdate from dual
;
spool procedureName.l og
exec procedureName
spool off
exit
EOC

[Sybrand Bakker]

Re: How to know in a unix script if oracle procedure executed or failed?

On 10 Dec 2003 13:18:38 -0800, c_vishal@hotmai l.com (vishal) wrote:

>Hi,
>
>I am writing my first script to execute a Oracle procedure through a
>unix script. Could you tell me how do I do error handling here? How do
>I know in my script if the procedure executed successfully or failed?
>Thanks
>
>#!/usr/bin/ksh
>sqlplus user/password@name <<EOC
>set time on
>set echo on
>select sysdate from dual
>;
>spool procedureName.l og
>exec procedureName
>spool off
>exit
>EOC

In the sql script include
whenever sqlerror exit failure
and the script will exit with a non-zero status.
If this is not sufficient look up whenever sqlerror and whenever
oserror on http://tahiti.oracle.com


--
Sybrand Bakker, Senior Oracle DBA

[=?iso-8859-15?Q?Thorsten_H=E4s?=]

Re: How to know in a unix script if oracle procedure executed or failed?

Hello,

you can use some other solution:

#!/usr/bin/ksh

>sqlplus user/password@name <<EOC>/tmp/log/log_file_unix_s hell.log
>set time on
>set echo on
>select sysdate from dual
>;
>spool procedureName.l og
>exec procedureName
>spool off
>exit
>EOC

you find the output of your user(SQL)-session in the
/tmp/log/log_file_unix_s hell.log-File.

best regards
thorsten häs

On Wed, 10 Dec 2003 23:49:22 +0100, Sybrand Bakker
<gooiditweg@syb randb.demon.nlw rote:

On 10 Dec 2003 13:18:38 -0800, c_vishal@hotmai l.com (vishal) wrote:
>

>Hi,
>>
>I am writing my first script to execute a Oracle procedure through a
>unix script. Could you tell me how do I do error handling here? How do
>I know in my script if the procedure executed successfully or failed?
>Thanks
>>
>#!/usr/bin/ksh
>sqlplus user/password@name <<EOC
>set time on
>set echo on
>select sysdate from dual
>;
>spool procedureName.l og
>exec procedureName
>spool off
>exit
>EOC

>
In the sql script include
whenever sqlerror exit failure
and the script will exit with a non-zero status.
If this is not sufficient look up whenever sqlerror and whenever
oserror on http://tahiti.oracle.com
>
>
--
Sybrand Bakker, Senior Oracle DBA

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

[Sybrand Bakker]

Re: How to know in a unix script if oracle procedure executed or failed?

On Thu, 11 Dec 2003 19:28:33 +0100, Thorsten Häs
<eifel-power@t-online.dewrote:

>Hello,
>
>you can use some other solution:
>
>#!/usr/bin/ksh

>>sqlplus user/password@name <<EOC>/tmp/log/log_file_unix_s hell.log
>>set time on
>>set echo on
>>select sysdate from dual
>>;
>>spool procedureName.l og
>>exec procedureName
>>spool off
>>exit
>>EOC

>
>you find the output of your user(SQL)-session in the
>/tmp/log/log_file_unix_s hell.log-File.
>
>best regards
>thorsten häs
>

He wants to know *in the script* whether his procedure succeeded., not
by looking at log files. Your solution doesn't work, so why contradict
me with a non-working solution where my solution does work?


--
Sybrand Bakker, Senior Oracle DBA

[Billy Verreynne]

Re: How to know in a unix script if oracle procedure executed or failed?

Sybrand Bakker <gooiditweg@syb randb.demon.nlw rote

He wants to know *in the script* whether his procedure succeeded., not
by looking at log files. Your solution doesn't work, so why contradict
me with a non-working solution where my solution does work?

Exactly.

In addition you can also declare a bind variable in SQL*Plus and set
it in anon PL/SQL blocks in the script (kind of like a process status
code). Then terminate the script with the EXIT command using the bind
variable as parameter.

Also, the OP should note that the exitcode in Unix is a byte, while
the actual exitcode in SQL*Plus (especially when using SQL%SQLCODE to
return ORA error numbers) is two bytes. Thus you cannot determine the
actual Oracle error code in the Unix script by using the exitcode (in
that case you will need to grep the spoolfile).

--
Billy

[Ubiquitous]

Re: How to know in a unix script if oracle procedure executed or failed?

vishal <c_vishal@hotma il.comwrote:

: I am writing my first script to execute a Oracle procedure through a
: unix script. Could you tell me how do I do error handling here? How do
: I know in my script if the procedure executed successfully or failed?
: Thanks

What I do is run the script with the output piped to a log file and within
the script itself, test for errors...
Within the stored procedure itself, there's a bunch of EXCEPTION handling
clauses with more specific error messages.

$HOME/bin/qs "execute load_table('X') "
echo "load_table has executed"

if [ $? -ne 0 ]
then
echo "An error has occurred"
exit
fi

err=`grep 'ORA-' $HOME/logfile|wc -1
if [ $err -ne 0 ]
then
echo "An ORACLE error has occurred"
exit
fi

Hope this helps!