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

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.

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)

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.

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.

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

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.

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

> Thus users have to do Oracle-specific coding rather than standards.

Each vendor sells his own version of the truth about standards.

> *Maybe* the issue is wanting to use existing standards-based client code?

Ah.. so now it is fat client instead of thin client?

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

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

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.

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

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

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.

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.

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

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

> 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 too solve.. only client-server has been there, done that, and a lot better that Java/JDBC can at the moment.

> 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. You would not use the family sedan to pull a plough on a farm field.

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.

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.

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.

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 blox smacks of ignorance (as I have said repeatedly, and proven, to our Java developers).

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.

--
Billy