Re: Simple Error trapping in SQLplus in Oracle
Date: 18 May 2003 20:18:22 -0700
Message-ID: <4baec153.0305181918.67554c63_at_posting.google.com>
Sybrand,
postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote in message news:<a20d28ee.0305160218.57368464_at_posting.google.com>...
> pentzol_at_hotmail.com (Lourens Pentz) wrote in message news:<4baec153.0305151544.10b4bb05_at_posting.google.com>...
> > Hi,
> >
> > I do the following using ksh - scripting on an Oracle database:
> >
> > +++++++++++++++++++
> > sqlplus username/PASS_at_Database << EOF
> >
> >
> > update table1 set field1 = 'xxx' where field1 = 'yyyy';
> >
> >
> >
> > insert into table2
> > (field1,
> > field2,
> > field3)
> > values ('value1',
> > 'value2',
> > 'value3);
> >
> >
> >
> > insert into table3
> > (field1,
> > field2,
> > field3)
> > values ('value1',
> > 'value2',
> > 'value3);
> >
> >
> > insert into table4
> > (field1,
> > field2,
> > field3)
> > values ('value1',
> > 'value2',
> > 'value3);
> >
> >
> > commit;
> > exit;
> > EOF
> >
> >
> > +++++++++++++++++++
> >
> > The question is : How can I do an error trap on each SQL statement, so
> > that the moment it generates an error (doesn't matter what), it does a
> > rollback and exit out of the sqlplus ??
> >
> > Cheers
> > Lourens
>
> whenever sqlerror exit failure
> This will however do statement level rollback
> If the statements constitute one transaction wrap them in
> begin
>
> exception
> when-others
> rollback;
> end;
> /
>
> Please don't consider this forum as a replacement for reading documentation.
> Your question is very elementary.
>
> Regards
>
> Sybrand Bakker
> Senior Oracle DBA
Received on Mon May 19 2003 - 05:18:22 CEST