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

Billy Verreynne wrote:

> Joseph Weinstein <bigdog_at_bea.com> wrote
>
> > 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
>
> Yes, but not with that syntax. Look at something like OWA_UTIL's
> CELLSPRINT that returns (in a firehose manner), a SELECT statement as
> an HTML table. Or using something like Mime type text/csv and using
> HTP.PRINT to returns data in CSV format.

Can it return the data in exactly the same way the DBMS returns it from fresh SQL?

> The "problem" is
> a) ignorance about Oracle
> b) assuming that Oracle looks and works like SQL-Server

Yep.

> > so the results would come back to the client in the same way as simple SQL?
>
> You are confusing SQL with the data format returned. There are no
> relationship between them. The data format which is returned can be
> anything from a cursor, ref cursor, html, xml and so on in Oracle.

Maybe the customer is confused too. He wants the data to come back like it does with fresh SQL, but from a procedure. You can say he is ignorant. I wouldn't be so harsh.

> > 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.
>
> It can. Look up reference cursors. (only, the OP does not want to use
> a reference cursor)

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

> > I'll certain take your advice under serious consideration. Am I wrong that
> > PL/SQL can't do something that sending simple SQL can?
>
> It can do even more. You can define a row object type. You can use
> what is called a pipe line table function to return rows from a stored
> procedure where the procedure can take a cursor as input. In addition,
> this procedure is by default threadsafe and can be use for parallel
> processing.

Yes, it is a wonderful thing. I guess you have zero sympathy for what the customer actually wants in this case. 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.

> > > 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?
>
> That the client software does not determine the server software in
> coporate IS/IT strategy

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. To the extent that standards are adhered to, that is their purpose too. To the extent that this goal is achievable, it is seen as a beneficial one, and BEA is big because big companies want this independence.
Regards,
Joe

>

>
>
> --
> Billy
Received on Tue Oct 21 2003 - 01:56:21 CDT