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

Hello Joe,

Firstly, let me apologize for my erroneously assuming that you were unfamiliar with other database engines.

Please see in-line:

 In the cases where a customer really
> wants read-data not to change for the duration of a transaction, the notion
> of getting rereads from a copy can seem dissembling, and Oracle did implement
> "SELECT ... FOR UPDATE" as a workaround.

Please elaborate why you are calling a rather simple tool (select for update) a 'work-around'. The statement is used for what is known as pessimistic locking. One can resort to a technique called optimistic locking, in WEB environment, in order to avoid using 'select for update' if need be.

>Don't get me started about
> Oracle's clunky implementation of the SERIALIZABLE isolation level either ;-)

Oracle currently does not implement the SERIALIZABLE isolation level in the strict sense of the word. What is known as the Oracle serializable isolation level, is commonly considered to be the SNAPSHOT IL.
> I had to prove to oracle and to our mutual customers that Oracle could silently
> commit failed transactions in serializable mode, and that the bug had been
> known for years. The issue is (was?) that if in serializable mode a transaction ...[skipped]..>

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.

Whilst I am unhappy myself about the Oracle level of 'bugginess' and the amount of time it takes to fix bugs, I was bitten more often by the locking scheduler behaviour in the READ COMMITTED mode. It comes to one as quite a surprise when one discovers that, under the locking scheduler, aggregate statements can return inconsistent data in the RC mode, especially if one is accustomed to always seeing statement level consistent data under Oracle.

Rgds. Received on Mon Oct 20 2003 - 17:47:02 CDT