How to Dynamically Name Table in Oracle SQL Select Statement?

Question

i would like to return all rows and fields from a table, where neither the tablename nor the fieldnames are known in advance. Something like:

select * from [TABLENAME]

this other method looks promising, but select * from test_cursor gives "attempt to access rows of an item whose type is not known..." https://stackoverflow.com/a/101064/209942

EXECUTE IMMEDIATE seems promising, but i read that returning multiple rows requires a cursor, and i can't find an example.

Prefer a solution that's very simple and fast (ie, would like to avoid row-by-row processing).

Would like to avoid creating a function or procedure, but maybe that's unavoidable. Maybe i need to use a table function?

Maybe something like the following?

CREATE OR REPLACE FUNCTION GetTable(table_name CHAR)

BEGIN

EXECUTE IMMEDIATE 'CREATE TABLE temp_table
  AS (SELECT * FROM :1)' USING table_name;

END;

SELECT * FROM table (temp_table)

thx

Check package [DBMS_SQL](https://docs.oracle.com/database/121/ARPLS/d_sql.htm#ARPLS058), there you can do full dynamic SQL statements. — Wernfried Domscheit, May 14 '16 at 18:28

johny why · Answer 1 · 2016-05-14T20:33:46.043

This answer does not require any knowledge of field types or number.

The short answer is:

EXECUTE IMMEDIATE 'CREATE TABLE temp_table AS (SELECT * FROM ' ||table_name||')';

SELECT * FROM temp_table;

The long answer is:

DROP TABLE TEMP_TABLE;

DECLARE table_name VARCHAR2(200);

BEGIN
  table_name := 'subreports';
  EXECUTE IMMEDIATE 'CREATE TABLE temp_table AS (SELECT * FROM ' ||table_name||')';
END;

SELECT * FROM temp_table;

Or, as a procedure (anyone, correct my syntax):

create procedure GetTable (table_name in VARCHAR2(200), result_set out sys_refcursor)
   is
   begin
    DROP TABLE TEMP_TABLE PURGE;
    EXECUTE IMMEDIATE 'CREATE TABLE temp_table AS (SELECT * FROM ' ||table_name||')';
      open result_set for select * from temp_table;
   end;

Pandi · Answer 2 · 2020-07-30T17:16:31.600

1

I create a variable by using user_tables.which is store to table name.

  Create procedure dynamic_tab_name(tab_name user_tables.table.name%type)
  As
     Type refc is ref cursor;
     Cur refc;
     Result varchar2(30);
    Begin
       Open cur for 'select a from'|| tab_name;
       Fetch cur into result;       
       dbms_output. put_line(result);
       Close cur;
    End;

edited Jul 30 '20 at 17:16

answered Jul 23 '20 at 12:55

Pandi

11
2

score 0 · Answer 3 · answered May 14 '16 at 17:05

The hardest part of your requirement is the datatype consistency of the columns you want to get, and number of columns that may be different between each tablename you want to select.

For your question, you could use:

dynamic SQL and Global Temporary Table : only if fieldname1 and fieldname2 always have datatype match col1 and col2 of your global_temp (you must create global_temp table first).

delete global_temp;
sql_stmt := 'insert into global_temp(col1,col2) 
             SELECT '|| fieldname1 || ',' || fieldname2
             ||' FROM ' || var_table_name;
EXECUTE IMMEDIATE sql_stmt;
select * from global_temp; --do any thing with result temp table

dynamic SQL into records (loop each row to get results): go to link, search emp_rec

How to Dynamically Name Table in Oracle SQL Select Statement?

3 Answers3