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

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.
>
> You've lost me there.

I understand.

> There's no "limitation" on Oracle PL/SQL
> procedures. They work as designed and implemented. And often far above
> and beyond the abilities and capabilities of other stored procedure
> languages.
>
> PL/SQL is not a macro language. It is a fully fledged programming
> language. Hell, Oracle Applications have over 4 million lines of
> PL/SQL code.

You can be sure that I am suitably impressed! The limitation of Oracle procedures is this. You cannot return query results from an oracle procedure except through an output parameter. Ie: an Oracle procedure cannot be a simple select in the same way you would send the SQL as a fresh query. This is a limitation because there is no standard provision for retrieving query data in that manner in JDBC. Thus users have to do Oracle-specific coding rather than standards.

> So what then is the issue? IMO, wanting to treat PL/SQL as a macro
> language to substiture a SELECT statement with a MACRO command call.
> If that is what turns you on, use Transact SQL. :-)

*Maybe* the issue is wanting to use existing standards-based client code? A T-SQL procedure isn't just a macro. It *is* precompiled and is a shareable execution plan, so it is a full-fledged procedure. I wouldn't call PL_SQL a full fledged programming language, because it seems to lack a simple stdout! The clear fact is that it lacks a simple capability that sending simple SQL has!

> > Don't forget the value of standards. 'Me Too' can be exactly
> > what a customer wants. There can be significant risk and cost to tieing
> > ones self to a single supplier.
>
> That is bullshit Joe. It does not matter *what* you use, you *are*
> tying yourself down to a single supplier and single technology.

If you say so ;-)

> Even
> when it comes to Java - or are you saying that it does not tie you
> down to Sun?

Nope. We make our own JVM, and participate in the design of all relevant Java APIs. The APIs are public specs that anyone can implement. I just wish Oracle did a better job! They wouldn't have spent so much money in three completely different attempts at a J2EE-standards-compliant application server if they didn't disagree with you...

> If anything, a single supplier is often exactly the right thing to do.
> Put the corporate IS/IT strategy hat on and think a bit.

But it'd have to be oracle, correct? ;-) The reason BEA is doing so well is that we do work everywhere, and do not tie customers to one vendor. We do see customer convert from IBM to us, and because of standards, they do so easily. We have seen it the other way too, just very rarely. We are successful, and we do a good job of implementing new standard APIs quickly and completely and performantly. Oracle would have been nothing if it didn't originally and strongly commit to the SQL standard.

> > In the case of how Oracle procedures can
> > return query data, this isn't a case of 'different and better', it's more a
> > case of 'different, and get used to it if you want to work with oracle'.
>
> Not from the client side. As for the server side, we are working with

> Oracle after all. So what is the big deal?

Two points:
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 Received on Sun Oct 19 2003 - 19:05:25 CDT