plsql - Oracle PL/SQL - How can I insert multiple rows using dynamic SQL -

i want go through tables , copy ids , tablename table. since have use variable table name, tried immediate execute. dynamic sql insert statement allows single rows.

the following sql statement it, if immediate execute allow insert muplitple rows.

declare     sqlstat varchar2(500);  begin    tname in (   select table_name all_tab_cols column_name='id'   )    loop     sqlstat := 'insert storetab (id,table_name) select id, '' :1 '' :2';     execute immediate sqlstat using tname.table_name,tname.table_name;   end loop; end; 

how can loop through tables , collect records?

an insert ... select can insert multiple rows, regardless if executed through "plain" sql, through pl/sql or through dynamic sql.

but can't use placeholders identifiers (names) in dynamic sql. need concatenate query. , don't need use single quotes if passing values through using clause:

declare     sqlstat varchar2(500); begin    tname in (select table_name all_tab_cols column_name='id' , table_name = 'foo')   loop     sqlstat := 'insert storetab (id,table_name) select id, :1 '||tname.table_name;     execute immediate sqlstat using tname.table_name;   end loop; end; / 

this will insert many rows storetab there rows in source table.

---

somewhat unrelated, but:

all_tab_columns can potentially return same table name multiple times, if current user has access tables in different schemas. should either handle selecting owner all_tab_columns , adjust theselectpart of dynamic sql accordingly. or useuser_tab_cols` instead if interested in tables owned current user.