Re: REF CURSORs and/or PL/SQL records

I guess you could actually just open a ref cursor selecting the variables from dual, not even using the record type, which would be a "weak" cursor. But I try not to use them... "strong" cursors are faster.

Jeff

"Jeff Smith" <jsmit234_at_ford.com> wrote in message news:as2h4e$3953_at_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:29:39 CST