Re: Simple Error trapping in SQLplus in Oracle
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