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

> Hello Joe,
> Unfortunately, your posting is somewhat misleading for those hoping
> to achieve code portability between different databases.
>
> 1. Yes, Oracle does not conform to the JDBC standard with respect to
> returning a ResultSet from an SP. That's regrettable but not a major
> issue since a customer can get a result set at the expense of one or
> two more java statements as other posters pointed out.

Hi! Thanks for this. To *me* it's not a major issue, and I hope it is not a major issue for the original poster either. Your statement that it is an issue at all is a refreshing corroboration.

> Speaking of
> standards, one would imagine that MS SQL Server inability to implement
> the row constructor (SQL '92) is more of an issue. E.g. a simple
> statement like this is incorrect in SQL Server:
> select * from t1 where (x,y,z) in (select x,y, z from t2);

Maybe! Depends on what the customer wants, but I would certainly be in favor of all relatiional DBMSes fully implementing SQL '92 and beyond.

> 2. More importantly, since judging by your postings you were not
> exposed to other databases beyond MS SQL Server,

see below ;-)

> you probably do not
> realize that simply adhering to the SQL'92 standard or the JDBC
> standard does not make your code vendor-independent. Database engines
> differ at more fundamental levels such as concurrency, isolation
> levels, query consistency. E.g. MS SQL server is a locking scheduler
> whilst Oracle represents so-called multiversioning family (as do MS
> Exchange, Interbase, Postgress).
>
> Today, treating a DB engine as a block box is a dangerous approach
> and vendor independence is a myth. Moving code from one DB engine to
> another is a serious project that cannot be solved just by being
> standard compliant.

Oh, absolutely! There are certainly a whole minefield of individualities from various DBMSes that one has to be aware of when either porting from one DBMS to another, or simply creating a performant application in one DBMS.

   As to Oracle's locking scheme, it has it's advantages and disadvantages, just like pessimistic locking does. 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. Don't get me started about Oracle's clunky implementation of the SERIALIZABLE isolation level either ;-) 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 inserted a row, and then updated it, the update could on occasion silently not find the newly-inserted row, and not update it. This is(was?) oracle bug# 440317. If the insert causes an index block to split, the subsequent read will cause the index block to be rolled back *to maintain read consistency* - so the update cannot find the row to update! I got a sympathetic support guy to recount the history of the bug, and it was known way back before the mid-90's when I had to discover it myself, and it's only progress at oracle to that date was to that they updated the fix-version to the one after the current version four times. He asked a developer why it never got addressed, and was told it was because 'it would be hard to fix'.

   A lot of my job is tracing problems from the complex application through complex middleware, and down to the DBMS, often having to prove that though Weblogic is what the user is interacting with, the problem is all the way through to the DBMS (MS, Sybase, Oracle etc) thus my position is informed, neutral cynicism regarding the trustworthiness of all major DBMSes, dealing with them all on a more or less regular basis. MS, Informix, Sybase, and DB2 also have their stories. I can understand the benefit of committing to one DBMS and becoming fluent at it's strengths and at accomodating or avoiding it's idiocyncracies. I was just chipping in to soften the notion that "there's nothing wrong with Oracle, it's all the original poster's/MS's fault". Regards,
Joe Weinstein at BEA

>
>
> Rgds.
Received on Mon Oct 20 2003 - 10:59:15 CDT