Return to 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..." http://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

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

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..." http://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).

thx

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..." http://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

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..." http://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.

thx

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..." http://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).

thx