Re: Oracle Procedure Question- Please.

Gary, As a matter of fact, Oracle stored procedures CAN be coded which return full result sets - they use PL/SQL tables as OUT parameters. The tricky part is to have client software capable of accepting such returned parameters. To support full result set retrieval from stored procedures, we have asked Oracle to
customize their Glue product, which they have done and which works almost flawlessly.. Unfortunately, it does
not appear that Glue will be long lasting, with Oracle Objects now being the preferred product. Also unfortunately, Oracle Objects does not support the retrieval of full result sets from stored procedures.

This capability is available to all who wish to code using OCI - the obndra function can bind PL/SQL tables to arrays in your host program. Not the most straightforward method, however. It amazes us how Oracle continues to ignore the fact that full result set retrieval is very useful, especially to
those of us who have made stored procedures an integral part of our architecture.

Oracle's attempt to pass cursor variables to the calling routine so that they can perform client-side fetches is not the answer, in my opinion. The fetch logic remains in the client, making for an unnecessarily complex code when a simple stored procedure call should do the trick.

Perhaps it would be useful if more of us would petition Oracle to fully support
full result set retrieval in all its middleware and client products. Maybe then
Oracle could compete with Sybase in this regard.

My $.02.

Mike Bernier
Database Consultant
AT&T GIS
Dayton, OH
>==========Gary Gapinski, 4/20/95==========
>
>Oracle stored procedures do not return result sets. They can at best
>return a cursor to a result set. I know of no workaround other than to
>use views wherever possible. If you do not need to have procedural
>results manifest as a result set, look into the DBMS_OUTPUT package,
>although that will not be of much use to an application that expects
>results to be prepared as if they had been SELECTed. Also, in recent
>releases, user-declared functions can be cited in SELECT statements.
>
>Using temporary tables will probably be more of a bother than it's worth.
>

Mike Bernier
Global Database Development and Administration AT&T Global Information Solutions Received on Tue Apr 25 1995 - 00:00:00 CEST