Re: Unix script help
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 2 Mar 2004 22:19:44 +0200
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@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
