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

0
votes

I want to go through all tables and copy the IDs and the tablename into a table. Since I have to use a variable as a table name, I tried it with IMMEDIATE EXECUTE. But with dynamic SQL the INSERT INTO statement allows only single rows.

The following SQL Statement would be it, if IMMEDIATE EXECUTE would allow INSERT INTO with muplitple rows.

DECLARE
    sqlStat VARCHAR2(500);

BEGIN 
  FOR TName IN (
  SELECT TABLE_NAME FROM all_tab_cols WHERE column_name='ID'
  )

  LOOP
    sqlStat := 'INSERT INTO storeTab (ID,TABLE_NAME) SELECT ID, '' :1 '' FROM :2';
    EXECUTE IMMEDIATE sqlStat USING TName.TABLE_NAME,TName.TABLE_NAME;
  END LOOP;
END;

How can I loop through tables and collect records?

2
oracleplsql

2 Answers

5
votes

An insert ... select can insert multiple rows, regardless if it is executed through "plain" SQL, through PL/SQL or through dynamic SQL.

But you can't use placeholders for identifiers (names) in dynamic SQL. You need to concatenate the query. And you don't need to use single quotes if you are passing values through the USING clause:

DECLARE
    sqlStat VARCHAR2(500);
BEGIN 
  FOR TName IN (SELECT TABLE_NAME FROM all_tab_cols WHERE column_name='ID' and table_name = 'FOO')
  LOOP
    sqlStat := 'INSERT INTO storetab (ID,TABLE_NAME) SELECT ID, :1 FROM '||tname.table_name;
    EXECUTE IMMEDIATE sqlStat USING TName.TABLE_NAME;
  END LOOP;
END;
/

This will insert as many rows into storetab as there are rows in the source table.

Somewhat unrelated, but:

all_tab_columns can potentially return the same table name multiple times, if the current user has access to those tables in different schemas. You should either handle that properly by also selecting the owner from all_tab_columns and adjust theselectpart of the dynamic SQL accordingly. Or useuser_tab_cols` instead if you are only interested in tables owned by the current user.

0
votes

I prefer using USER_TAB_COLUMNS as there is not need to append schema name with the table name. If we are using ALL_TAB_COLS then it is better to use OWNER.

You can also frame your query as below by using USER_TAB_COLUMNS.

DECLARE
    sqlStat VARCHAR2(500);
BEGIN 
  FOR TName IN (SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE column_name='ID')
  LOOP
    sqlstat := 'INSERT INTO storeTab (ID,TABLE_NAME) SELECT ID, ''' ||TName.TABLE_NAME|| ''' FROM '||Tname.TABLE_NAME ;
    EXECUTE IMMEDIATE sqlStat ;
  END LOOP;
END;

Or while fetching from ALL_TAB_COLS

DECLARE
    sqlStat VARCHAR2(500);
BEGIN 
  FOR TName IN (SELECT OWNER,TABLE_NAME FROM ALL_TAB_COLS WHERE column_name='ID')
  LOOP
    sqlStat := 'INSERT INTO storeTab (ID,TABLE_NAME) SELECT ID, ''' ||TName.TABLE_NAME|| ''' FROM '||Tname.OWNER||'.'||Tname.TABLE_NAME;
    EXECUTE IMMEDIATE sqlStat  ;
  END LOOP ;
END ;