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

Billy Verreynne wrote:

> Joseph Weinstein <bigdog_at_bea.com> wrote i
>
> > 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.
>
> If the cursor resides entirely on the client-side, with no
> rereferences server-side, how are transactions on that cursor handled?
>
> Sure, client-side cached transactions are "nice" and fast. But when
> these are flushed to the database there is the possibility of either
> overwriting another user's changes, or sitting with the problem that
> your changes to your cache cannot be applied server side as your cache
> copy is out of date.

The poster didn't want to alter data, they just wanted to read it! No cursor is necessary at all!

> Another fallacy in thinking that that you are freeing the DBMS to do
> other work when using the raw hosepipe method. You are not. (read the
> Oracle Concepts manual before making statements like that)

LOL! You are *so* oracle-centric. *Other* DBMSes *do* obtain extra performance, and *do* free themselves from further consideration of the write by firehosing the return data. If Oracle can't, so be it.

> Besides, the correct way of processing data is server side and not
> client side. I shudder when I see client code pulling down a bunch of
> rows and then looping through each row and changing it.

I agree with you! Clients getting cursors at all, sucks IMHO. But unless you claim that clients should never even ask for data, we have a problem. The original poster just wants the data from his wuery. He's not updating, just reading. He's saying "PLEASE, ORACLE, JUST SEND ME THE DATA, JUST LIKE I'D SENT "SELECT * FROM FOO".
> That is what we used to do back in the 70' and early 80's using Cobol
> and tapes and then ISAM/VSAM files. Row-by-row processing like that
> has no place in RDBMS.

True, but that's not what the poster was doing.

> > The limitation of Oracle procedures
> > is this. You cannot return query results from an oracle procedure except
> > through an output parameter.
>
> How is that different from a T-SQL macro that uses an implicit output
> parameter definition? It is treated exactly the same from the client
> side - a reference cursor.

Again, no. The data can come (from MS for instance) all in one return packet, which the client can read in whatever form, with no further DBMS involvement needed. The point is that in MS, SQL operates the same for the client, whether it is sent fresh and compiled, or in a procedure. An MS procedure *can* redirect query data in a procedure, but it's default is to sent it to the client. Oracle *can* send query data directly to the client, in the case that it is sent fresh. The original poster just wanted the oracle procedure to return data in the same way.

> Sure, it may require a tad more coding in PL/SQL as you're dealing
> with a formal programming language and not a macro language. Thus the
> procedure header definition and the like. But by the same tokem,
> having that formal programming language allows you much greater
> flexibility than using something like T-SQL.

So there is more overhead. I don't disagree. And you claim that the benefit is more capability. Maybe. That's a better position to take, and *if* the customer wants that added capabilities, rather than wanting what he wants at the moment, you have made the deal.

> > 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.
>
> An Oracle procedure can return a reference cursor. Thus, from
> something like Delphi I can call an Oracle stored proc doing a SELECT
> on my behalf the same way as I can call a T-SQL macro doing a SELECT.
>
> So I do not undertand your problem.. unless you're saying that JDBC
> lacks? If so, that is a JDBC and Java problem.

Yes, and it is any JDBC user's problem. If it was an easy problem for Oracle to fix (implementing the JDBC spec), oracle might decide to own part of the problem and fix it. Unfortunately the problem is not easy to fix, and oracle doesn't need to fix it, so it isn't oracle's problem.

> > Thus users have to do Oracle-specific coding rather than standards.
>
> Each vendor sells his own version of the truth about standards.

I'm not sure what you're saying here... Are you saying there's disagreement on the JDBC spec, for instance?

> > *Maybe* the issue is wanting to use existing standards-based client code?
>
> Ah.. so now it is fat client instead of thin client?

I hope not, at least as far as SQL is concerned, but to turn you back to the original issue, the poster just wants simple query results coming back the same way whether he just says "SELECT * FROM FOO", or runs a procedure that just says "SELECT * FROM FOO". This is apparently the user's first chill in the education about DBMS application porting. Some gentle understanding is warranted.

> > 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!
>
> It does have a standard output. Refer to the DBMS_OUTPUT package.

But why does simple SQL work the way the poster wants, but a procedure not? Are you saying that the procedure could do something (logicall) like:

SELECT * FROM FOO > DBMS_OUTPUT so the results would come back to the client in the same way as simple SQL?

> > The clear fact is that it lacks a simple capability that sending simple
> > SQL has!
>
> With a PL/SQL procedure I can output a CSV file to a web browser. I
> can return a reference cursor to a VB/Delphi/Powerbuilder client. I
> can perform data transformation via a pipe line table function and
> return the resulting dynamically created cursor to the caller.
>

I'm sure PL/SQL is a mighty language with wonderful capabilities, but that isn't the current point. The poster just found one little thing PL/SQL (to my paltry knowledge) can't do.

> Do not blame PL/SQL for your lack of understanding about it. And yeah,
> it *is* a full blown programming language which can be compiled to
> machine code.

I'll certain take your advice under serious consideration. Am I wrong that PL/SQL can't do something that sending simple SQL can?

> > 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...
>
> Exactly. And IBM's implementation is not the same either. Or
> Microsoft's. Java is write once and debug everywhere. :-)

If you say so ;-) That's true with Oracle too! Back in the 90's I had to work for months to get oracle to admit my proof that despite their documentation, that OCI was not threadsafe until 7.3.4, and then only on Solaris and NT. Other platforms like HP would not be threadsafe  until 8.0. You see, Oracle has completely separate code lines for each of these platforms...

> When you buy into any technolgy, you are dependant on that and on the
> vendor that provides you that technology. Open standards mean very
> little from a corporate strategic perspective. Corporate IS/IT
> strategy is strategic long term partnerships - not changing vendors
> every 3 years 'cause they have bought into "open standards".

If you say so ;-) We must be selling to different customers.

> The whole concept of a corporate's prime criteria is for end-user
> systems that can run against any database/server platform, is a
> *blatant* untruth.

A conspiracy, that's what it is. Far from being a prime consideration, it should never be attempted or contemplated. The fools.

> Any idea what a corporate spend on their server architecture ito
> maintenance, support, investment in staff and so on? The cost of the
> client software fades into insignificance against it.

And your point is?

> > > 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? ;-)
>
> No. If Microsoft is your partner then you use (and abuse) their
> technologies and make it work for you. Ditto for database software.
> Corporate desktop software. Server hardware vendor. Network hardware
> supplier. Etc.
>
> I'm a all or nothing kind of guy. Even though I may not personnally
> like SQL-Server, if that is the corporate server platform then *that*
> is what you leverage. 100%. No half meassures. And the client
> applications need to fit *that* and not vice verse. The server
> platform is what drives IT strategy as that is the biggest investment.

So be it! Some do make good business selling stuff that must be highly performant, and DBMS-neutral to a high degree. It's not seemless, and sometimes not easy, but it can be done.

> > Oracle would have been nothing if it didn't originally and strongly
> > commit to the SQL standard.
>
> Untrue. Oracle was the first database that did RDBMS and the one that
> has lead ever since. Not because of its commitment to SQL - that was
> not the deciding factor.

If you say so! ;-) LOL!

> > Two points:
> > 1 - wrong. It is different at the client side in the case that you want
> > to use a JDBC standard client.
>
> Between the two of us Joe, I think JDBC and Java are solutions looking
> for problems to solve..

Well sure! And finding them! Our customers are doing wonderful things, and are generally very happy. If oracle didn't see that market, ti wouldn't be falling all over itself (three separate code attempts and a J2EE server) to do the same thing.

> only client-server has been there, done that,
> and a lot better that Java/JDBC can at the moment.

Client-server is dead. The internet provides too many users for it. DBMSes are inherently client-server. Even for such quaint old DBMS-biased benchmarks as TPC-C, Oracle uses middleware like transaction monitors to get optimal performance. The small point that started this thread is just a little clunkiness in the way oracle procedure can talk to anyone, client or middleware.

> > 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.
>
> Joe, you would not use a Ferrari to go grocery shopping, having to
> take the wife and kids with.

Ferraris are temperamental. You're right, but I have a wonderful Porsch which is typically better engineered to serve plebean uses without complaint or failure. Ferrari would make more money if they made cars that were fast *and* reliable. They do adhere to the standards for "driver API". It used to be that you could get an Italian car which flashed the lights when you pressed the center of the steering wheel, and honked when you pulled back on the indicator stem! (Maserati)

> You would not use the family sedan to
> pull a plough on a farm field.
>

*I* wouldn't, but others do. For instance, in the mid '80s PeopleSoft's Payroll application was a bare-bones port of some ISAM row-by-row COBOL program to 'client-server' by simply separating the data from the logic by the network, using RDBMS client calls. They not only did row-by-row processing at the client, they actually did 'system queries' to retrieve application SQL that they store in the DBMS as strings, and retrieve to the client to send back to the DBMS for application functions! For all I know they still do the same, and a payroll program that *isn't rocket science* runs for hours when it could take minutes if done right.

> Both are cars (databases). Both 4 wheels and an engine and a roof
> (stored procs). But to effectively use the vehicle, you need to apply
> it properly.

No argument there. Most of the benefits of stored procedures is in simply *using them*. The precompile re-use helps, but the major benefit is putting your bulk data thrashing where the data is. You build your saw mills where the trees are.

> My problem is people wanting to treat Oracle like it is SQL-Server and
> expecting Oracle to provide the same functionality and command set and
> implementation like SQL-Server.

I understand, but I see people who are wanting to convert one way or the other all the time. I try to help, and from their perspective.

> If that is the case, then why not use SQL-Server (and with my
> blessings ;-). That is then the product that best suits your
> requirements.

Yes, except when there is, and it *does* happen, the corporate mandate that some DBMS independence (or even simply change) be created.

> If you are using Oracle, then use Oracle's features, best practise
> methods and strenghts.
>
> And if you write end-user systems that can run on both, then it is
> your job to ensure that you correctly use SQL-Server or Oracle when
> the customer runs your system on it. Treating a database like a black
> box smacks of ignorance (as I have said repeatedly, and proven, to
> our Java developers).

Oh, absolutely. The question is how much deep knowledge is needed, and to what extent customers want standards to allieviate the issue. Cars have substantially converged in their interface, and to everyone's benefit. Some people have a valid desire that DBMSes do the same.

> Besides, your customer wants your product to run on their platforms
> using their servers. The fact that your product also supports blue
> widgets in pink addresses do not feature at all.. unless they have a
> need for pink dresses for their blu widgets.
>
> I.e. corporate IT/IS question is not what other server platforms do
> you support. The question is how well does your product support my
> platform.

Ok, until they call, saying they're switching platform from ABC to XYZ. Some actually forsee switching platforms, and do ask. Regards,
Joe Weinstein at BEA

>
>
> --
> Billy
Received on Mon Oct 20 2003 - 12:10:12 CDT