Calling stored procs and functions in PL/SQL using Cursor Variables

From: Dominic Klein <NOdominic.klein_at_ventura-uk.comSPAM>
Date: 1998/04/15
Message-ID: <3535c918.18139513_at_194.169.119.101>#1/1

[Quoted] I've got a package on the server with stored procs and functions on and I want to be able to retrieve values from these using cursor variables. Now I've declared my package and procs and functions OK and they compile but I can't retrieve them using DBMS_OUTPUT.PUTLINE. I have declared the cursor as :

type BLOB_REC is record

(

      thing_id blobtest.thing_id%TYPE
     )    
     ;

type BLOBTEST_TYPE is ref cursor return BLOB_REC;

in the simple procedure i just do this ...

procedure p_surname

(

      p_surname out varchar2,
      p_cur_rec in out BLOBTEST_TYPE
     ) 

is
l_surname varchar2(5);
begin

     p_surname := 'KLEIN';
     open p_cur_rec for 
     select 
        THING_ID
     from
        BLOBTEST
     ;

end; --p_surname

which just returns 2 values as there are 2 recs in the table.

Anyway my problem is getting the values to the screen from the calling program (which will be on the client) and is :

set serveroutput on;
declare
l_surname varchar2(5);
l_thing_id AM001.BLOBTEST_TYPE;
Begin

     AM001.p_surname(l_surname, l_thing_id);
     dbms_output.put_line (AM001.f_test||' '||l_surname||' '||
                           l_thing_id.thing_id);

End ;
/

I'm trying to reference l_thing_id as though it is an element in a record.
If i take the DBMS line out it doesn't complain but i don't know what it's done so that's not much use.
Anyway can anyone shed some light on this please ? All the books I've seen just give examples for selecting * which is not much use !

Cheers. Received on Wed Apr 15 1998 - 00:00:00 CEST