Thanks Ron, that was actually what I meant to write... but it's getting late here ;) Tanel. ----- Original Message ----- From: "Ron Thomas" <rthomas@xxxxxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Tuesday, March 02, 2004 11:08 PM Subject: Re: Unix script help > > Except I'd change your if statement to test ERRCODE instead of $?. Some shells have a nasty (and > correct) habit of setting $? to 0 after the assignment. > > if [[ $ERRCODE -ne 0 ]] ; then > > Ron Thomas > Hypercom, Inc > rthomas@xxxxxxxxxxxx > "The box said I needed to have windows 98 or better...So I installed linux." > > > > tanel.poder.003@ma > il.ee To: oracle-l@xxxxxxxxxxxxx > Sent by: cc: > oracle-l-bounce@fr Subject: Re: Unix script help > eelists.org > > > 03/02/2004 01:19 > PM > Please respond to > oracle-l > > > > > > > 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 > ----------------------------------------------------------------- > > > > > > ---------------------------------------------------------------- > 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 -----------------------------------------------------------------