Re: Get values from REF CURSORS in Stored Procedures

teoman wrote:

> I want to know how can I get the values from ref cursors in stored procedures?
> I give a little example:
>
> create or replace procedure mytest(ReturnCursor in out ref_cursor.mycursor,
> "PART_NUMBER" in Varchar2) is
> p_part_number varchar2(40) := "PART_NUMBER";
> sqltext varchar2(2000);
> begin
> sqltext := 'select * from product where part_number = '''|| p_part_number||'''';
> open returncursor for sqltext;
> end mytest;
>
> If i want use the procedure like this
>
> declare
> myc ref_Cursor.mycursor;
> cursor cr
> is
> select * from product;
> rec cr%rowtype;
> begin
> mytest(myc,'PAT-LIC-ADL');
> fetch myc into rec;
> dbms_output.put_line(rec.DESCRIPTION);
> end;
>
> thats ok. But I don't have in all cases the cursor definition. I need "variant"
> variables or something like that. How can i manage it?

Your example mixes REF CURSOR and STATIC cursor and the thought that anyone would put DMBS_OUTPUT.PUT_LINE into a stored procedure sends chills down my spine. Why?

Anyway in any cursor you can always refer to any value as cursor.fieldname just like it was any other variable.

Now please get rid of that DBMS_OUTPUT unless it is just there for testing purposes.

Daniel Morgan Received on Tue Jul 02 2002 - 10:38:23 CDT