SQL & PL/SQL

Hello,
I want to apply BULK COLLECT and FORALL feature for a insert statement in my procedure for performance improvements as it has to insert a huge amount of data.

But the problem is that the insert statement gets generated dynamically and even the table name is found at the run-time ... so i am not able to apply the performance tuning concepts.

See below the code

PROCEDURE STP_MES_INSERT_GLOBAL_TO_MAIN
  (P_IN_SRC_TABLE_NAME                  VARCHAR2 ,
  P_IN_TRG_TABLE_NAME                  VARCHAR2 ,
  P_IN_ED_TRIG_ALARM_ID                 NUMBER ,
  P_IN_ED_CATG_ID                 NUMBER ,
  P_IN_IS_PIECEID_ALARM IN CHAR,
  P_IN_IS_LAST_RECORD IN CHAR
  )
IS
      V_START_DATA_ID                 NUMBER;
      V_STOP_DATA_ID                   NUMBER;
      V_FROM_DATA_ID                   NUMBER;
      V_TO_DATA_ID                       NUMBER;
      V_MAX_REC_IN_LOOP              NUMBER := 30000;
      V_QRY1         VARCHAR2(32767);
BEGIN
            EXECUTE IMMEDIATE 'SELECT MIN(ED_DATA_ID), MAX(ED_DATA_ID) FROM '|| P_IN_SRC_TABLE_NAME INTO V_START_DATA_ID , V_STOP_DATA_ID;
            --DBMS_OUTPUT.PUT_LINE('ORIGINAL START ID := '||V_START_DATA_ID ||' ORIGINAL  STOP ID := ' || V_STOP_DATA_ID);
            V_FROM_DATA_ID := V_START_DATA_ID ;
            IF (V_STOP_DATA_ID - V_START_DATA_ID ) > V_MAX_REC_IN_LOOP THEN
                    V_TO_DATA_ID := V_START_DATA_ID + V_MAX_REC_IN_LOOP;
            ELSE
                   V_TO_DATA_ID := V_STOP_DATA_ID;
            END IF;

          LOOP 
                
                BEGIN
                 
             LOOP       
                        V_QRY1 := ' INSERT INTO '||P_IN_TRG_TABLE_NAME||
                        ' SELECT * FROM '||P_IN_SRC_TABLE_NAME ||
                        ' WHERE ED_DATA_ID BETWEEN ' || V_FROM_DATA_ID ||' AND ' || V_TO_DATA_ID;
                EXECUTE IMMEDIATE V_QRY1;
commit;
          
                                 V_FROM_DATA_ID :=  V_TO_DATA_ID + 1;
                        IF  ( V_STOP_DATA_ID - V_TO_DATA_ID > V_MAX_REC_IN_LOOP ) THEN
                              V_TO_DATA_ID := V_TO_DATA_ID + V_MAX_REC_IN_LOOP;
                        ELSE
                              V_TO_DATA_ID := V_TO_DATA_ID + (V_STOP_DATA_ID - V_TO_DATA_ID);
                        END IF;
                EXCEPTION
                         WHEN OTHERS THEN.............
....................so on 

Now you can observer here that P_IN_SRC_TABLE_NAME is the source table name which we get as a parameter at run-time. I have used 2 table in the insert statement P_IN_TRG_TABLE_NAME (in which i have to insert data) and P_IN_SRC_TABLE_NAME(from where i have to insert data)

  V_QRY1 := ' INSERT INTO '||P_IN_TRG_TABLE_NAME||
                        ' SELECT * FROM '||P_IN_SRC_TABLE_NAME ||
                        ' WHERE ED_DATA_ID BETWEEN ' || V_FROM_DATA_ID ||' AND ' || V_TO_DATA_ID;
                EXECUTE IMMEDIATE V_QRY1;

now when i appy the bulk collect and forall feature i am facing the out of scope problem....see the code below ...

BEGIN
            EXECUTE IMMEDIATE 'SELECT MIN(ED_DATA_ID), MAX(ED_DATA_ID) FROM '|| P_IN_SRC_TABLE_NAME INTO V_START_DATA_ID , V_STOP_DATA_ID;
            --DBMS_OUTPUT.PUT_LINE('ORIGINAL START ID := '||V_START_DATA_ID ||' ORIGINAL  STOP ID := ' || V_STOP_DATA_ID);
            V_FROM_DATA_ID := V_START_DATA_ID ;
            IF (V_STOP_DATA_ID - V_START_DATA_ID ) > V_MAX_REC_IN_LOOP THEN
                    V_TO_DATA_ID := V_START_DATA_ID + V_MAX_REC_IN_LOOP;
            ELSE
                   V_TO_DATA_ID := V_STOP_DATA_ID;
            END IF;

          LOOP 
                DECLARE
                 TYPE TRG_TABLE_TYPE IS TABLE OF P_IN_SRC_TABLE_NAME%ROWTYPE;
                 V_TRG_TABLE_TYPE TRG_TABLE_TYPE;
                 CURSOR TRG_TAB_CUR IS 
                 SELECT * FROM P_IN_SRC_TABLE_NAME
                 WHERE ED_DATA_ID BETWEEN V_FROM_DATA_ID AND V_TO_DATA_ID;
                 V_QRY1 varchar2(32767);
                BEGIN
                 
                OPEN TRG_TAB_CUR;
                
                LOOP
                FETCH TRG_TAB_CUR BULK COLLECT INTO V_TRG_TABLE_TYPE LIMIT 30000;
                 
                FORALL I IN 1..V_TRG_TABLE_TYPE.COUNT
                 
                V_QRY1 := ' INSERT INTO '||P_IN_TRG_TABLE_NAME||' VALUES V_TRG_TABLE_TYPE(I);'
            
                EXECUTE IMMEDIATE V_QRY1;
                        
                EXIT WHEN TRG_TAB_CUR%NOTFOUND;
                END LOOP;
                CLOSE TRG_TAB_CUR;
                
                        V_FROM_DATA_ID :=  V_TO_DATA_ID + 1;
                        IF  ( V_STOP_DATA_ID - V_TO_DATA_ID > V_MAX_REC_IN_LOOP ) THEN
                              V_TO_DATA_ID := V_TO_DATA_ID + V_MAX_REC_IN_LOOP;
                        ELSE
                              V_TO_DATA_ID := V_TO_DATA_ID + (V_STOP_DATA_ID - V_TO_DATA_ID);
                        END IF;
                EXCEPTION
                         WHEN OTHERS THEN.........so on 

But the above code is not helping me , what i am doing wrong ??? how can i tune this dynamically generated statement to use bulk collect for better performace ......

Thanks in Advance !!!!