Dynamic PL/SQL -> REF CURSOR in a procedure

From: Georges Ko <gko_at_gko.net>
Date: 15 Aug 2000 09:44:52 +0800
Message-ID: <ybysns747sb.fsf_at_gko.net>

    Hello,

[Quoted]     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 - 03:44:52 CEST