Re: [GENERAL] How to stop implicit rollback on certain errors?

"Ross J. Reedstrom" wrote:
>
> Hmm, sounds like a vote for nested transactions. The JDBC driver developer
> (Peter Mount) was musing that nested transaction would make large object
> support easier for him, as well.
>
> As to the other example of Oracle not forcing a rollback, I have a feeling
> that this may be specific to syntax errors in an interactive session.
> Implementing this sort of behavior has been discussed recently on the
> hackers list, in the context of making it easier to work interactively
> inside a transaction.
>
> I would be surprised if Oracle allows non-syntax errors inside a
> transaction to be ignored, or ignores anything in a non-interactive
> session. How about testing an example like links, where you provide data
> in a format the backend can't handle, (an out of range int or date or
> something) and see how Oracle handles that.
>

Here my test on Oracle:

$ sqlplus scott/tiger
SQL*Plus: Release 8.0.5.0.0 - Production on Mon Dec 13 23:22:31 1999
(c) Copyright 1998 Oracle Corporation. All rights reserved.

Connected to:
Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production

SQL> create table test(id int primary key,i numeric(12,3), d date);
Table created.

SQL> insert into TEST VALUES(1,999999999.999,'28-FEB-1999');
1 row created.

SQL> insert into TEST VALUES(1,111111111.999,'29-FEB-1999')
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input
string

SQL> insert into TEST VALUES(1,999999999999.999,'28-FEB-1999')
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column

SQL> commit;
Commit complete.

SQL> select * from test;
ID I D
---------- ---------- ---------
1 1000000000 28-FEB-99

SQL> insert into TEST VALUES(1,1.999,'10-JAN-1999')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C001590) violated
SQL> insert into TEST VALUES(2,1.119,'10-MAR-1999');
1 row created.

SQL> select * from test;
ID I D
---------- ---------- ---------
1 1000000000 28-FEB-99
2 1.119 10-MAR-99
SQL> rollback;
Rollback complete.

SQL> select * from test;
ID I D
---------- ---------- ---------
1 1000000000 28-FEB-99

SQL> exit
Disconnected from Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production

Jose'

> Who's right? Well, as Peter Eisentraut said, what Postgres implements is
> the _definition_ of a transaction: all together, or nothing at all. This
> isn't just an arbitrary rule: the validity of the relational calculus
> depends on transactional semantics.
>
> Ross
>
> --
> Ross J. Reedstrom, Ph.D., <reedstrm(at)rice(dot)edu>
> NSBRI Research Scientist/Programmer
> Computer and Information Technology Institute
> Rice University, 6100 S. Main St., Houston, TX 77005
>
> On Thu, Dec 09, 1999 at 09:23:35AM +0800, Lincoln Yeoh wrote:
> > At 01:18 AM 09-12-1999 +0100, Peter Eisentraut wrote:
> > >Seriously, why do you use a transaction, when you don't want any errors
> > >caught? Transactions are defined as everything succeeds or nothing goes.
> > >If you want update to succeed anyhow, put it in it's own transaction
> > >(i.e., commit before it).
> >
> > I want errors caught, most errors abort everything but some errors I want
> > to try a different update instead, if that doesn't work then only rollback
> > everything.
> >
> > >> I guess that's expected, and I should insert big years using another less
> > >> ambiguous format. What is the recommended format?
> > >
> > >The safest way would be to set a date format with SET DATESTYLE TO and use
> > >that, possibly assisted by library formatting routines.
> >
> > OK.
> >
> > Link.
> >
> >
> > ************
> >
>
> ************