Dynamic SQL - Stored Proc

[thermidor]

Hi All,

I need to create a stored procedure that will receive the name of a table as an input parameter and return all of the records in that table in a ref cursor. Can anyone tell me how to do this or point me to an online reference with instructions for how to do this? I've googled, but as yet haven't found anything to fit the bill.

TIA,
Sven

 

[bogman]

Hi,

Something along the lines of the following code might be what you want:

CREATE OR REPLACE PACKAGE my_pck
IS

TYPE my_ref_cursor_type IS REF CURSOR ;

PROCEDURE get_records ( p_tab_in VARCHAR2,
p_records OUT my_ref_cursor_type ) ;

END my_pck ;
/
CREATE OR REPLACE PACKAGE BODY my_pck
IS

PROCEDURE get_records ( p_tab_in VARCHAR2,
p_records OUT my_ref_cursor_type )
IS

BEGIN

OPEN p_records FOR ' SELECT *
FROM ' || p_tab_in ;


END get_records ;

END my_pck ;
/

 

[thermidor]

Thanks Bogman!