How to use cursor in Oracle?

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.

Ans:

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

Hosted by www.Geocities.ws