select from unknown table table Dynamic SQL

[AnthonyGeorge]

I need to do a select from a table that is unknown until runtime. Is there any good examples of this. The Store Procedure will take in the table name and return a row.

I am not using inline SQL because there is a lot more complexity involved . What I need to do is understand how to reference this unknown table.


Here is an example of what I have written so far.

PACKAGE pk_LoadNativeDataItems IS

PROCEDURE version;

TYPE RowDataCur IS REF CURSOR;

PROCEDURE pr_LoadNativeDataItems(p_flag IN NUMBER
p_how_many_rows_to_fetch IN NUMBER,
p_row_data OUT RowDataCur);


PROCEDURE pr_LoadNativeDataItems(p_flag IN NUMBER
p_how_many_rows_to_fetch IN NUMBER,
p_table_name IN VARCHAR,
p_row_data OUT RowDataCur) IS


BEGIN
OPEN p_row_data FOR
SELECT * from ||p_table_name||;
END pr_LoadNativeDataItems;

END pk_LoadNativeDataItems;

Thanks for any help

Tony

 

[AnthonyGeorge]

For anyone else with this problem here is my fix


PROCEDURE pr_LoadNativeDataItems(p_flag IN NUMBER,

p_how_many_rows_to_fetch IN NUMBER,

p_table_name IN VARCHAR2,

p_tmp_table_name IN VARCHAR2,

p_row_data OUT RowDataCur) IS



truncate_text VARCHAR2(100);

sql_text VARCHAR2(300);


v_table_name VARCHAR2(20) := p_table_name;

v_tmp_table_name VARCHAR2(20) := p_tmp_table_name;




BEGIN

IF p_flag = -1 THEN

truncate_text := 'TRUNCATE TABLE ' || p_tmp_table_name;

EXECUTE IMMEDIATE truncate_text;

sql_text := 'SELECT * FROM (SELECT ROWNUM, tbl.* FROM ' || v_table_name || ' PARTITION (P1) tbl

WHERE FILENAME IS NULL ORDER BY m.NATIVEDATA_ID)

WHERE ROWNUM < '||p_how_many_rows_to_fetch;

OPEN p_row_data FOR sql_text;

ELSE

sql_text := 'SELECT * FROM (SELECT ROWNUM, tbl.* FROM ' || v_table_name || ' PARTITION (P1) tbl ,

'|| v_tmp_table_name ||' tmp_tble WHERE FILENAME IS NULL AND

tbl.NATIVEDATA_ID <> tmp_tble.NATIVEDATA_ID

ORDER BY m.NATIVEDATA_ID)

WHERE ROWNUM < '||p_how_many_rows_to_fetch;

END IF;


END pr_LoadNativeDataItems;