Popola LISTA tabelle target da una lista di tabelle sorgente (BULK COLLECT e FORALL)

Occorre popolare alcune una lista di tabelle dall'ambiente di produzione (sorgente) a quello di test (target).
In ambiente di test creiamo un db link tra i due schema che hanno le stesse tabelle.

In ambiente di test abbiamo creato l'utente " testimp"  e il database link  "dbltest" verso l'utente  prodimp di produzione.

OWNER	DB_LINK	USERNAME	HOST
TESTIMP	DBLTEST.WORLD	PRODIMP	PROD01

In ambiente di produzione abbiamo creato l'utente "prodimp" e due tabelle A e A1. Le stesse sono state poi create poi in ambiente di test.

La procedura ha come parametri in ingresso il numero di righe da fa leggere dalla istruzione fetch bulk collect, il numero di righe dopo il quale effettuare la commit e la lista delle tabelle.

CREATE OR REPLACE PROCEDURE testimp.popolatabellabulk3 ( righe IN NUMBER, ncommit IN NUMBER, tabelle IN VARCHAR2)
IS
v_error_code NUMBER;
v_error_msg VARCHAR2(512);
TYPE a_rt  IS TABLE OF a@dbltest%ROWTYPE;
TYPE a1_rt IS TABLE OF a1@dbltest%ROWTYPE;
l_a   a_rt;
l_a1  a1_rt;

CURSOR V_CUR IS select regexp_substr(tabelle ,'[^,]+', 1, level) As tblname from dual
                       connect by regexp_substr(tabelle, '[^,]+', 1, level) is not null;

CURSOR a_cur IS SELECT * FROM a@dbltest;
CURSOR a1_cur IS SELECT * FROM a1@dbltest;

vcommit NUMBER := 0;
     
BEGIN

For tbls IN V_CUR
LOOP
    dbms_output.put_line('Tabella: '||tbls.tblname);
 
CASE upper(tbls.tblname)
         WHEN 'A' THEN
BEGIN
   vcommit:= 0;
   execute immediate ('truncate table testimp.a');
   OPEN a_cur;
   LOOP
   FETCH a_cur BULK COLLECT INTO l_a LIMIT righe;
   dbms_output.put_line('letti: '||l_a.COUNT);
        FORALL indx IN 1 .. l_a.COUNT
        INSERT INTO A VALUES l_a(indx);
 
        vcommit := vcommit + l_a.COUNT;
     
        if vcommit >= ncommit THEN
           commit;
           vcommit := 0;
        end if; 
     
   EXIT WHEN a_cur%NOTFOUND;   
   END LOOP;
 
   commit;
 
   EXCEPTION
    WHEN OTHERS THEN BEGIN
        v_error_code := SQLCODE;
        v_error_msg := SQLERRM;
        IF a_cur%ISOPEN THEN CLOSE a_cur;
        END IF;
        raise_application_error(-20001,'popolatabellabulk3-ERRORE -  '||SQLCODE||' -ERROR- '||SQLERRM);
    END;   
   CLOSE a_cur;
END;
        WHEN 'A1' THEN
BEGIN
   vcommit:= 0;
   execute immediate ('truncate table testimp.a1');
   OPEN a1_cur;
   LOOP
   FETCH a1_cur BULK COLLECT INTO l_a1 LIMIT righe;
   dbms_output.put_line('letti: '||l_a1.COUNT);
        FORALL indx IN 1 .. l_a1.COUNT
        INSERT INTO A1 VALUES l_a1(indx);
 
        vcommit := vcommit + l_a1.COUNT;
     
        if vcommit >= ncommit THEN
           commit;
           vcommit := 0;
        end if; 
     
   EXIT WHEN a1_cur%NOTFOUND;   
   END LOOP;
 
   commit;
 
   EXCEPTION
    WHEN OTHERS THEN BEGIN
        v_error_code := SQLCODE;
        v_error_msg := SQLERRM;
        IF a1_cur%ISOPEN THEN CLOSE a1_cur;
        END IF;
        raise_application_error(-20001,'popolatabellabulk3-ERRORE -  '||SQLCODE||' -ERROR- '||SQLERRM);
    END;   
   CLOSE a1_cur;
END;
   ELSE dbms_output.put_line('tabella: '||tbls.tblname||' non prevista.');
  END CASE;
END LOOP;
 
END popolatabellabulk3;
/