Re: Re : Returning result of a simple "select * " from a stored procedure without using cursor

VC wrote:

> Joseph Weinstein <bigdog_at_bea.com> wrote in message news:<3F94D568.18F3932F_at_bea.com>...
>
> Hello Joe,
>
> [VC]
>
> > > > This was true under some older Oracle versions. However, the bug was
> > > > fixed in May 2002 (patchset 8.1.7.4) and it's hardly fair to discuss
> > > > the database behaviour in the context of a fixed software bug. If
> > > > you're saying that the bug is still there, please post (or send me
> > > > via e-mail) a reproducible test case. I'd very much interested to
> > > > learn from your experience.
> > >
>
> [JW]
> > > Ok! It was in all oracle versions we'd dealt with from '96 till it was fixed,
> > > and it was known for years. I believe the recent "fix" is simply to throw an
> > > exception, failing the transaction in that case. The failure message is not so
> > > explicit as to say "we can't complete this tx because your insert caused an
> > > index block to split, and we won't be able to find the row(s) you inserted".
> > > We get the generic 8177 error. I will attach a SQL-PLUS script and jdbc
> > > program that you can use to duplicate the problem, and please let me know
> > > what it does for you. Doing it in Java using oracle's thin driver did give
> > > an 8177 on the 110th transaction. This is the "cannot serialize this transaction"
> > > message, which I have to explain to customers when they ask why the DBMS
> > > cannot serialize a tiny transaction that is just like the other hundreds they've done
> > > successfully, especially when there's only one user logged in to the DBMS.
>
> I've just run the script on Oracle 8.1.7.4 and the bug is still there:
>
> ===================
> Connected to:
> Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
> With the Partitioning option
> JServer Release 8.1.7.4.0 - Production
>
> SQL> begin
> for i in 1..200 loop
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> insert into isolationtest (id,val) values (i,100);
> update isolationtest set val = 200 where id = i;
> COMMIT;
> end loop;
> end;
> 2 3 4 5 6 7 8 9
> 10 /
> begin
> *
> ERROR at line 1:
> ORA-08177: can't serialize access for this transaction
> ORA-06512: at line 4
> ===================================
>
> It is indeed a very serious problem. Thank you for sharing your
> painfull experience.
>
> It appears to have been fixed in 9.2, though (see my previous
> posting).
>
> Rgds.

Thanks, but you haven't grasped the full ugliness: You are seeing the *FIX*, as far as Oracle is concerned! The original bug can be shown on 7.3.4, which is that the script completes without complaint, and the ending query cheerfully shows a few inserted rows where the val is still 100!!! Oracle "fixed" the problem of silent partial transactions being committed to the DBMS, by merely failing a transaction whose only unknowable fault is having an insert that happens to cause an index block split!!!

Joe Weinstein at BEA Received on Tue Oct 21 2003 - 11:48:46 CDT