Re: Any Way to use DBMS_SQL with a Ref Cursor?

On Wed, 07 Apr 1999 02:34:33 GMT, ghubert_at_netpath.net (Gene Hubert) wrote:

>The only efficient way to get large amounts of data into a
>PowerBuilder datawindow (via a stored procedure) is to use a procedure
>that takes an in/out ref cursor parameter. I'd really like to do this
>and have the select statement (really just the where clause) built
>dynamically in a string. The result set for a given stored procedure
>would always be fixed and known at compile time. So far, I can't find
>any way to make it work. Is there any way I could do this with
>DBMS_SQL?
>
>I have often been amazed at things I can do with Oracle that I would
>never have guessed from looking at the documentation and am hoping
>this will be another one.
>
>Thanks for any ideas,
>
>Gene Hubert
>Qualex Inc.
>Durham, NC

Unfortunatly, no, a DBMS_SQL cursor is a handle to cursor rather than a cursor itself (that is why it is declared as an integer).

We ran into this same problem and came up with two solutions. First, the stored procedure creates a SQL statement string and returns this to the client, which then opens and manages its own cursor. The second was to open the cursor on the server and put the results into one or more PL/SQL tables which are then passed back to the client. The client then makes multiple calls to the server until all the data has been returned. At that point the cursor is closed by the stored procedure.

Obviously, the second option only works if the cursor returns relatively small amounts of data (~2000 rows max).

--
Mark Wagoner
To reply, remove no.spam from my e-mail address Received on Wed Apr 07 1999 - 08:07:24 CDT