Re: REF CURSORs and/or PL/SQL records

From: Jeff Smith <jsmit234_at_ford.com>
Date: Wed, 27 Nov 2002 08:25:34 -0500
Message-ID: <as2h4e$3953@eccws12.dearborn.ford.com>

Probably a number of ways to do it... here is a quick one.

SQL> CREATE OR REPLACE PACKAGE pkg_code AS   2

  3     type io_cur IS REF CURSOR;
  4     procedure sp_return_cursor_of_vars ( o_cursor OUT io_cur );
  5
  6     type REC_vars IS REF CURSOR;
  7     TYPE type_name IS RECORD (
  8        col1 VARCHAR2(10),
  9        col2 NUMBER(2)
 10     );

 11
 12 END pkg_code;
 13 /

Package created.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY pkg_code AS   2
  3 PROCEDURE sp_return_cursor_of_vars ( o_cursor OUT io_cur ) IS   4 record_var type_name;
  5 BEGIN

  6     record_var.col1 := 'STRING';
  7     record_var.col2 := 1;
  8
  9     OPEN o_cursor FOR
 10     SELECT record_var.COL1,
 11            record_var.col2
 12     from dual;

 13
 14
 15 END sp_return_cursor_of_vars;
 16
 17 END PKG_CODE;
 18 /

Package body created.

SQL>
SQL> /* UNIT TEST */
SQL> DECLARE
  2     TYPE tmp_cur is ref cursor;
  3     RETURN_CURSOR tmp_cur;
  4     --vrecord_var pkg_code.REC_VARS;
  5     VCOL1 VARCHAR2(10);
  6     VCOL2 NUMBER(2);
  7  BEGIN
  8     PKG_CODE.SP_RETURN_CURSOR_OF_VARS( RETURN_CURSOR );
  9     fetch RETURN_CURSOR into
 10        vcol1,
 11        vcol2;
 12     DBMS_OUTPUT.PUT_LINE('col1 from OSP: ' || vcol1 );
 13     DBMS_OUTPUT.PUT_LINE('col2 from OSP: ' || to_char(vcol2) );
 14     IF RETURN_CURSOR%ISOPEN THEN
 15        close RETURN_CURSOR;
 16     END IF;

 17
 18 EXCEPTION
 19 WHEN OTHERS THEN

 20     IF RETURN_CURSOR%ISOPEN THEN
 21        close RETURN_CURSOR;
 22     END IF;
 23     DBMS_OUTPUT.PUT_LINE('UNIT TEST EXCEPTION');

 24 END;
 25 /
col1 from OSP: STRING
col2 from OSP: 1

PL/SQL procedure successfully completed.

"Harry Boswell" <hboswel1_at_bellsouth.net> wrote in message news:h9p7uukeij6u50ou7137nna8iva9h09t45_at_4ax.com...
> I've written a set of stored procedures that fetch into a set of variables
> that are part of a PL/SQL record, which is then returned via a cursor.
But
> the programmer developing the web front end is wanting it returned
> differently - I think what he's asking for is a REF CURSOR. Where I'm
> having trouble understanding is the variables in the PL/SQL record that
> aren't part of the query - i.e., I assign default values to some variables
> because they aren't contained in our tables, but I want to return a
> "complete" set of data, rather than just those data items we have.
>
> If I understand REF CURSORs correctly, the calling app gets the format
> information about the returned data from the underlying Oracle table via
the
> query. So how would I return format information about those items that I
> don't retrieve with the query (for instance, Legislative_District_Num is
> returned as a null; COuntryName is always "USA".
>
> Thanks,
> Harry Boswell
Received on Wed Nov 27 2002 - 07:25:34 CST