oracle - How to return a table without knowing its structure in advance? -

oracle - How to return a table without knowing its structure in advance? -

everyone need create function in oracle accepts table name , homecoming collection content of based table accepted.

i've been doing search, many examples of form: first, define table type; then, fill table of type , homecoming it.

but won't know construction of collection need returned until function called, can not define table type @ time of programming.

how can create it? thanks:-)

what want this: say, have 3 tables--table_a, table_b, table_c--each of has different columns. need create function func(table_name) take table name(table_a, table_b, or table_c) , homecoming collection content of determined table name passed function. 3 tables have different columns, can't create type "tcolumndata " created. so, how should write function?

one approach utilize global temporary tables.

create or replace procedure test_procedure(table_name varchar2) begin declare table_or_view_not_exist exception; pragma exception_init(table_or_view_not_exist, -942); begin begin execute immediate 'truncate table temp_table'; execute immediate 'drop table temp_table'; exception when table_or_view_not_exist dbms_output.put_line('table temp_table not found. skipping drop..'); end; execute immediate 'create global temporary table temp_table on commit preserve rows select * ' || table_name || ' '; end; end test_procedure;

then run procedure:

execute test_procedure('table_a');

once procedure has completed execution, can verify by:

select * temp_table;

oracle plsql