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

Joseph Weinstein <bigdog_at_bea.com> wrote in message news:<3F9326C5.232BB383_at_bea.com>...
> Billy Verreynne wrote:
>
> > Joseph Weinstein <bigdog_at_bea.com> wrote :
> >
> > > All JDBC drivers for MS, including their own, will return data in the default
> > > firehose mode, unless configured specifically not to. There is a good reason
> > > for it. Performance.
> >
> > And all ODBC drivers don't. Good reason for it. Performance. :-)
> >
> > It's all in the eye of the beholder Joe and whehther the performance
> > hole is round or square or another shape all together.
> >
>
> Good! So you do posit the possibility of "the other way" being legitimately
> superior to some? For example, a cursor-base protocol requires more actual
> client-dbms messaging, whereas just having the DBMS send everythin at once,
> frees the DBMS to spend it's cycles on other work.
>
> > > Ideally, a procedure
> > > would act exactly like the SQL it contains, except it would be faster because
> > > it is precompiled.
> >
> > So what is the problem then using a reference cursor?
>
> I am agnostic, but the ssmall point is that it's different than simple SQL, like a procedure
> that just did "select * from foo", and that was it, and you got your results back...
>
> > > In my area of knowledge, JDBC, this point
> > > is often repeated, and has to, because the limitation of oracle procedures,
> > > that they don't act exactly like the same SQL sent fresh, for simple
> > > queries. This has required Oracle to provide non-standard JDBC methods
> > > and settings in order to retrieve query data from procedures, where
> > > all other DBMSes, to my knowledge, provide this through the standard calls.
............
<skipped>

> 1 - wrong. It is different at the client side in the case that you want
> to use a JDBC standard client.
> 2 - Too blase. The original poster was a customer, and in my opinion
> it is better to take their point of view to the extent that one can. Probably
> this person is a M$ SQL customer as well as an Oracle customer, and I wouldn't
> take the position that "There's no limitation on Oracle PL/SQL procedures.".
> Otherwise they would do what the customer wants, which was to get
> query results back to the client without having to have a cursor specified
> in the procedure.
>
> > --
> > Billy
>
> regards,
> Joe Weinstein at BEA

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. 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);
2. More importantly, since judging by your postings you were not exposed to other databases beyond MS SQL Server, 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.

Rgds. Received on Mon Oct 20 2003 - 06:26:26 CDT