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

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.

> 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?

> 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. 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.

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. :-)

> 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. Even when it comes to Java - or are you saying that it does not tie you down to Sun?

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.

> 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?

--
Billy