Dynamically Populating an Oracle Cursor in a Stored Procedure

Question

I need to dynamically populate an Oracle cursor (Oracle 10g). The SQL statement changes, based off of the input value to pull from different tables, and columns. What I don't want to do is have to maintain a temporary table that I truncate and load everytime the sproc is executed. Here is what I am currently doing, but if there is another alternative I would appreciate the help:

Stored Procedure

    PROCEDURE Get_Type_One_Polygon_Values(in_role VARCHAR2, rc_generic OUT SYS_REFCURSOR) as
BEGIN        

  execute immediate 'truncate table teamchk.temp_type_one_roles';

  execute immediate 'INSERT INTO TEAMCHK.TEMP_TYPE_ONE_ROLES ' || 
                    'SELECT ' || in_role || '_POLY_ID, ' || in_role || '_POLY_NAME ' ||      
                    'FROM TEAMCHK.' || in_role;        

  open rc_generic for                     
        select * from teamchk.temp_type_one_roles;

END;

Temp Table

    CREATE TABLE TEAMCHK.TEMP_TYPE_ONE_ROLES
(
    ROLE_ID     NUMERIC(38,0),
    ROLE_NAME   VARCHAR2(75)        
);

Answer 1

That's easy, you can use dynamic cursors...

create or replace PROCEDURE Get_Type_One_Polygon_Values
(in_role VARCHAR2, rc_generic OUT SYS_REFCURSOR) as
sql varchar2(100);
BEGIN        
            sql :='SELECT ' || in_role || '_POLY_ID, ' 
                 || in_role || '_POLY_NAME '
                 || 'FROM TEAMCHK.' || in_role;        

            open rc_generic for sql;
END;

It may be beneficial to use column aliases POLY_ID and POLY_NAME to unify them all in the refcursor.

Answer 2

How can we call this procedure from function?