Create automatic generic script using meta data tables to insert into another table with same structure

Question

Basically I want to create a script which automatically select from one table and insert into new table in plsql(both tables structure are same). This has to be the generic one which can work for any table. Like by using the dba_tab_cols and just passing the table name in the loop(get the select query of the table) and use this for insert into the another table.

something like below, but this give me error bcoz of extra ',' in the INSERT INTO statement at the end SQL Error: ORA-00936: missing expression

       FOR i IN (SELECT 'v_rec(i).'||column_name||',' AS col 
                    FROM dba_tab_cols 
                   WHERE table_name=v_table_name AND owner = 'XYZ'
                ORDER BY column_id 
                  ) LOOP
           v_col := v_col||i.col;
        END LOOP;
      DBMS_OUTPUT.PUT_LINE(v_col);
     END;

     v_sql_data :='
     DECLARE 
        TYPE c_cur IS REF CURSOR;
        v_cur c_cur;
        TYPE t_table IS TABLE OF '||v_table_name||'%ROWTYPE;
        v_rec t_table;
        v_sql VARCHAR2(4000 CHAR);
     BEGIN
        v_sql := ''select * from '||v_table_name||''';
        OPEN v_cur FOR v_sql;
        LOOP
        FETCH v_cur  BULK COLLECT INTO v_rec LIMIT 10000 ;
        EXIT WHEN v_rec.COUNT=0;
      FORALL i IN 1..v_rec.COUNT
         INSERT INTO '||v_new_table_name||' VALUES ('||v_col||');
           COMMIT;
       END LOOP;
     END;
     ';
   EXECUTE IMMEDIATE v_sql_data;

Could anyone pls suggest.?

Thanks.

EdStevens EdStevens · Accepted Answer · 2020-12-08T18:07:08

I'd say your problem is not an extra comma, but all of those mis-placed and unmatched single-quotes. For starters:

v_sql_data :='

What's that single quote doing there all by itself? Everything that follows it is considered a literal string until the parser finds a terminating quote.

Again:

TYPE t_table IS TABLE OF '||v_table_name||'%ROWTYPE;

look at what is enclosed by those two quotes. Your code is full of this kind of stuff.

And we don't even know how your PL/SQL begins. A block (stored or anonymous) certainly doesn't begin with a FOR statement.

But, at the end of the day, you are making this WAAAYYYY more complicated than it need be. Why not a simple

create table_b as select * from table_a;

Create automatic generic script using meta data tables to insert into another table with same structure

1 Answers