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

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.

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

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

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

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

Try that with T-SQL. ;-)

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

--
Billy