sql - EXECUTE IMMEDIATE in plsql -

how result code

execute immediate 'select * ' || table_name 

through for loop

the usual method looks this

for items in (select * this_table) loop htp.p(items.id); end loop; 

if really need select * from dynamic table name, i'd go dbms_sql

type record:

create type tq84_varchar2_tab table of varchar2(4000); / 

type result set (which array of records):

create type tq84_varchar2_tab_tab table of tq84_varchar2_tab; / 

the function select , returns instance of result set:

create or replace function tq84_select_star_from_table(table_name in varchar2)      return tq84_varchar2_tab_tab     stmt_txt     varchar2(4000);     cur          number;     columns_desc dbms_sql.desc_tab;     column_cnt   number;      result_set   tq84_varchar2_tab_tab;  begin      stmt_txt := 'select * ' || table_name;          cur := dbms_sql.open_cursor;      dbms_sql.parse(cur, stmt_txt, dbms_sql.native);     dbms_sql.describe_columns(cur, column_cnt, columns_desc);     dbms_sql.close_cursor(cur);       stmt_txt := 'select tq84_varchar2_tab(';      in 1 .. column_cnt loop           if != 1            stmt_txt := stmt_txt || ',';         end if;          stmt_txt := stmt_txt || columns_desc(i).col_name;      end loop;      stmt_txt := stmt_txt || ') ' || table_name;  --  dbms_output.put_line(stmt_txt);      execute immediate stmt_txt      bulk collect result_set;      return result_set;   end tq84_select_star_from_table; 

the function can used like:

declare   records   tq84_varchar2_tab_tab; begin    records := tq84_select_star_from_table('user_objects');    in 1 .. records.count loop       dbms_output.put_line (records(i)(5) || ': ' || records(i)(1));   end loop;  end; /