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

From: VC <boston103_at_hotmail.com>
Date: 21 Oct 2003 07:12:44 -0700
Message-ID: <31e0625e.0310210612.5aa62f57@posting.google.com>

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. Received on Tue Oct 21 2003 - 09:12:44 CDT