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

Joseph Weinstein <bigdog_at_bea.com> wrote i

> Right. That's the paltry thing Oracle procedures can't do, act like simple SQL
> and return the data in the same way.

You are focusing on a very specific thing - no cursors. And then saying PL/SQL cannot meet the functionality looked for by the OP.

I beg to differ (besides the issue that PL/SQL *can* return data in non-cursor formats).

In VB/Delphi, you will do something like the following with T-SQL proc that does a SELECT.

a) create a stored proc object plugging in the stored proc name
b) create a data connector 
c) create a data grid

When the stored proc object is activated, the data (from the embedded SELECT) is displayed in the data grid.

Thus, to do the SELECT all you need to do is use the stored proc name and plug it into your app - no need to code a SELECT statement. You can even throw parameters as the stored proc (instead of coding a SELECT with a WHERE clause).

Well, the *exact* same thing *can* be done with a PL/SQL proc in Oracle.

As I said orginally - in *both* cases a cursor is returned by the stored proc (be that SQL-Server or Oracle).

Next I said that doing this in PL/SQL is a tad more complex (i.e. a few lines of additional code) as PL/SQL is a formal procedural language (with OO/OR features) and you need to declare header & variables explicitly - unlike T-SQL.

Thus my questioning *what* the OP was trying to do and *why* the fixation on not using a cursor (when doing this in T-SQL is an implicit cursor anyway!).

It seems to me that because of those two or three lines of additional code, he's bitching about how Oracle's implementation works. (and I'm likely wrong but then I have no facts to go on as he did not supply any)

> Yes, it is a wonderful thing. I guess you have zero sympathy for what
> the customer actually wants in this case.

Correct as the customer
a) did not state his problem in clear and unambigious manner b) do not want to consider using what works Oracle (and instead fixate on SQL-Server solutions)

Joe, what I really find frustrating is people who post problems to their "solutions" here... never stating *why* they selected this "solution" they did and what the original problem is.

How can we provide any meaningful answers? WHAT is the reason for not wanting to use a cursor? Once that is established as a true requirement (or not), then we can move on to possible solutions.

> I know he won't get it, but
> somehow I understand his puzzlement that the DBMS has to send me
> results in a different way from the same SQL if I send it fresh, or
> ask Oracle to compile it and run it when I say.

I would say that this puzzlement is caused by a lack of understanding how *Oracle* works.. and not applying how SQL-Server (or Informix, Ingres, DB2, etc) works to determine the problem's solution.

> I think you are trying to say that server software determines the
> client software? If so, the establishment of middleware is to
> break that dependency.

If middleware can deliver. Just remember that almost everything you can do in a middleware tier, can be done in the database tier.. and often better as the db tier is many times more mature and scaleable.

But this is getting way of topic. :-)

--
Billy