Re: Unix script help

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Tue, 2 Mar 2004 22:19:44 +0200
Message-ID: <054001c40093$b3dcc8c0$b8fb23d5@porgand>

Hi,

Put "whenever sqlerror exit failure rollback" in the beginning of your SQL script and check the exit status of sqlplus from shell using shell pseudovariable $?

A quick example:

script.sql:

whenever sqlerror exit failure rollback
create table ....
truncate table ....
exit

shell script:

sqlplus user/password @script.sql
ERRCODE=$?
if [ "$?" -ne "0" ]; then

echo Failure!
exit $ERRCODE
fi

That way, any failing SQL command causes sqlplus to exit immediately and return error code 1 to caller.
Note that you can replace the "failure" string in whenever sqlerror exit failure rollback with any integer up to 255 I believe to return custom error codes from different sections of your sql script. If you return a code larger than 255, it'll wrap.

Note that you can have similar check for OS commands called from sqlplus with host command as well, you have to define "whenever oserror exit failure rollback" in your script for example.

Tanel.

Original Message ----- From: "M Rafiq" <rafiq9857_at_hotmail.com> To: <oracle-l_at_freelists.org> Sent: Tuesday, March 02, 2004 10:07 PM Subject: Unix script help

> Hi
>
> I need help for following sql script. I want to put condition before
> truncate that table creation must be successful before truncate runs. The
> condition may be to check count before and after creation or tracking of
any
> ORA/warning message but I could not make up my mind. I want to put it in
> cron to run it weekly.
>
> Is any body can help to give me some tips/script itself?
>
> Regards
> Rafiq
>
>
> create table tangram.sw_save nologging
> tablespace tangram
> as select * from tangram.sw_use_t_h where sut_closed_dt > = (sysdate-10);
>
> truncate table tangram.sw_use_t_h;
>
> insert into tangram.sw_use_t_h
> select * from tangram.sw_save;
>
> commit;
>
> drop table tangram.sw_save;
>
> _________________________________________________________________
> Frustrated with dial-up? Lightning-fast Internet access for as low as
> $29.95/month. http://click.atdmt.com/AVE/go/onm00200360ave/direct/01/
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Mar 02 2004 - 14:17:02 CST