cursor_function.sql

/*********************
||  The following example shows CURSOR can be return in FUNCTION.
||The calling proc needs to declare RECORD in order to use this function.
||
||
*****************************/
CREATE OR REPLACE PACKAGE       pkg_aa 
AS
TYPE c_get_aa IS REF CURSOR;
FUNCTION f_aa(pin_contact_id IN NUMBER) RETURN c_get_aa;
END pkg_aa;
/
CREATE OR replace package body pkg_aa
as
FUNCTION f_aa(pin_contact_id IN NUMBER) RETURN c_get_aa
IS
c_test PKG_aa.c_get_aa;
BEGIN
OPEN c_test
FOR
     select * from contact
     where contact_id = pin_contact_id;


return c_test;

END f_aa;
END pkg_aa;
/

declare
c_temp PKG_aa.c_get_aa;
TYPE CursorRecord is Record (
 CONTACT_ID                  contact.CONTACT_ID%TYPE,                    
 BUSINESS_TYPE_CD            contact.BUSINESS_TYPE_CD%TYPE,               
 FEDERAL_TAX_NBR             contact.FEDERAL_TAX_NBR%TYPE,                
 LEGAL_NME                   contact.LEGAL_NME%TYPE,                      
 DOING_BUSINESS_AS_NME       contact.DOING_BUSINESS_AS_NME%TYPE,          
 SOCIAL_SECURITY_NBR         contact.SOCIAL_SECURITY_NBR%TYPE,            
 FIRST_NME                   contact.FIRST_NME%TYPE,                      
 MIDDLE_NME                  contact.MIDDLE_NME%TYPE,                     
 LAST_NME                    contact.LAST_NME%TYPE,                       
 TELEPHONE_NBR               contact.TELEPHONE_NBR%TYPE,                  
 FAX_NBR                     contact.FAX_NBR%TYPE,                        
 EMAIL_CD                    contact.EMAIL_CD%TYPE,                       
 CREATE_DT                   contact.CREATE_DT%TYPE,                      
 CREATE_USER_ID              contact.CREATE_USER_ID%TYPE,                 
 UPDATE_DT                   contact.UPDATE_DT%TYPE,                      
 UPDATE_USER_ID              contact.UPDATE_USER_ID%TYPE                 
   );
c_rec CursorRecord;

BEGIN
c_temp:=pkg_aa.f_aa(331070);
fetch c_temp INTO c_rec;
dbms_output.put_line(to_char(c_rec.contact_id));
dbms_output.put_line(c_rec.first_nme);

end;
/