Re: Simple Error trapping in SQLplus in Oracle

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 16 May 2003 03:18:18 -0700
Message-ID: <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 Fri May 16 2003 - 12:18:18 CEST

Original text of this message