Re: Dynamic PL/SQL -> REF CURSOR in a procedure

In Oracle 8i it can be done (see
http://osi.oracle.com/wa/ask/owa/ask_tom_pkg.display?p_dispid=479021456587); [Quoted] in 7.3 I would guess that it's not possible.

Georges Ko <gko_at_gko.net> schrieb in im Newsbeitrag: ybysns747sb.fsf_at_gko.net...
>
> Hello,
>
> I'm looking for a way to get a set of records from a procedure
> with Oracle 7.3.3.5.0 (PL/SQL 2.3.3.5.0), to be used with Crystal
> Reports 7. At Seagate's Web site, I found how to write a procedure
> that can be used by CR7 :
>
> TYPE duration_cursor IS REF CURSOR RETURN duration_type;
>
> (duration_type is a RECORD)
>
> CREATE OR REPLACE PROCEDURE duration_query(
> cursor IN OUT duration_cursor,
> custom_name VARCHAR2,
> custid VARCHAR2,
> .
> .
> .
> OPEN cursor FOR
> SELECT tkt.custom_name, tkt.cid, tkt.usrid,
> tkt.calling_nbr, tkt.called_nbr, tkt.xlated_nbr,
> .
> .
> .
>
> )
>
> This works, but the problem is that duration_query has a lot of
> parameters, thus, the SQL statement's WHERE part has many forms,
> according to the parameters.
>
> Do I have a simple way to deal with this in PL/SQL alone ? That
> is, not doing something like:
>
> IF ...
> OPEN cursor FOR
> SELECT ...
> WHERE ...
> ELSIF ...
> OPEN cursor FOR
> SELECT ...
> WHERE ...
> ELSIF ...
> OPEN cursor FOR
> SELECT ...
> WHERE ...
> ELSIF ...
> OPEN cursor FOR
> SELECT ...
> WHERE ...
> END IF;
>
> where the only differences would be in the WHERE parts.
>
> So, I took a look at dynamic PL/SQL, and I'm wondering, how do I
> link:
>
> - the "cursor" variable (of type REF CURSOR) used by CR7 to get the
> rows
>
> with
>
> - the "dynamic cursor" which is of type NUMBER (returned by
> DBMS_SQL.OPEN_CURSOR)
>
> Basically, I would like my procedure to look like this:
> CREATE OR REPLACE PROCEDURE ivpn_duration_query(
> cursor IN OUT ivpn_duration_package.ivpn_duration_cursor,
> custom_name VARCHAR2,
> .
> .
> .
> ) AS
> dynCursor NUMBER;
> numRows NUMBER;
> sqlQuery VARCHAR2(2048);
> BEGIN
> -- dynCursor := DBMS_SQL.OPEN_CURSOR; <----- (1)
> sqlQuery := ...
> BEGIN
> DBMS_SQL.PARSE(dynCursor, sqlQuery, DBMS_SQL.NATIVE);
> numRows := DBMS_SQL.EXECUTE(dynCursor);
> EXCEPTION
> WHEN OTHERS THEN
> IF SQLCODE != -942 THEN
> RAISE;
> END IF;
> END;
> -- DBMS_SQL.CLOSE_CURSOR(dynCursor); <----- (2)
> END duration_query;
>
> As the output should go to "cursor", I don't need
> DBMS_SQL.OPEN_CURSOR (1): can I get a valid dynCursor from a REF
> CURSOR ? As the data is for the caller, I don't need CLOSE_CURSOR (2)
> neither. Has someone an example?
>
> Thank you.
>
> Georges.
Received on Tue Aug 15 2000 - 12:19:35 CEST