Insert from one table into another with commit Intervals

Question

I have written a procedure to insert data from one table into another table. There is a huge amount of data - a total of 106 millions rows. So I want to put a commit interval on the query.

I have found this script that normally will work and will not take time to get the job done but I don't know why I get an error on

INSERT INTO dlc_pr_activity_remise   
VALUES (add all values using ga_array(i).<value in select>)

Errors:

Error(51,1): PL/SQL: SQL Statement ignored
Error(51,47): PL/SQL: ORA-00917: missing comma

My procedure :

create or replace
PROCEDURE ACTIVITY1 AS 
BEGIN
DECLARE 
commit_interval pls_integer := 250000 ; 
uncommitted pls_integer := 0; 
fetch_size  pls_integer := 2500 ; 
cursor g1 is SELECT prr.EVT_DECLENCHEUR ,
                    prr.BQ_CDF ,
                    prr.BQ_DOM ,
                    prr.NUM_COMMERCANT ,
                    prr.TYPE_CONTRAT_COM ,
                    prr.COMMERCANT_SIRET ,
                    prr.TYPE_PRE_COMP ,
                    prr.BQ_CDF_ID_EBF ,
                    prr.BQ_DOM_ID_EBF ,
                    prr.DAT_TRAITEMENT ,
                    prr.EVT_TYPE_OPERATION ,
                    prr.DEV_MT_CRE ,
                    prr.NBR_DECI_MT_CRE ,
                    prr.REF_FICHIER_TRAITE ,
                    prr.NUM_CTC ,
                    prr.REF_FICHIER_ORIGINE ,
                    prr.ORIGINE_FLUX ,
                    prr.NUM_EST_REMISE ,
                    prr.NUM_REMISE_ORIGINE ,
                    prr.NUM_MACHINE ,
                    prr.REF_ARCHIVAGE_REM ,
                    prr.DAT_REMISE ,
                    prr.SUPPORT_REMISE ,
                    prr.DAT_REMISE_CALCULEE ,
                    prr.DAT_VALEUR_REMISE ,
                    prr.COD_APPLI ,
                    to_date(SYSDATE,'DD/MM/YYYY')
                    FROM dlc_pr_remise prr
                    WHERE NOT EXISTS
                      (SELECT *
                      FROM DLC_PR_ACTIVITY_REMISE prao
                      WHERE prao.num_est_remise = prr.num_est_remise
                      );
TYPE GL_T is table of gl%rowtype ; 
gl_array GL_T; 
begin 
open gl ; 
loop 
fetch gl 
bulk collect 
into gl_array 
limit fetch_size ; 
forall i in 1 .. gl_arary.count 
INSERT INTO dlc_pr_activity_remise values(add all values using ga_array(i).<value in select>)
uncommitted :+ uncommitted + sql%rowcount ; 
exit when gl_arary.count < fetch_size ; 
if uncommitted >= commit_interval 
then 
commit ; 
uncommitted := 0; 
end if ; 
end loop ; 
commit ; 
close gl; 
END ;
END ACTIVITY1;

You got everything you need to achieve your requirment. Why dont you give a try and then come up if you face some issue. Do what has been suggested by @codemonkey and then post the issue if you face any — XING, Mar 29 '17 at 08:56
yes i am trying to do it i have just one error in : INSERT INTO dlc_pr_activity_remise values(add all values using ga_array(i).) — Anas Rafei, Mar 29 '17 at 08:59
it says Statement ignored and missing comma i dont know why for me all semms to be good — Anas Rafei, Mar 29 '17 at 09:00
Can you edit you question and put the code you tried and the error you got. — XING, Mar 29 '17 at 09:01
@XING do you have any idea of what can cause this , cuz for me it should work !! — Anas Rafei, Mar 29 '17 at 09:36
It would only work when it would be correct. You made mistakes which you need to correct it before making it work. See my answer. — XING, Mar 29 '17 at 09:42

XING · Accepted Answer · 2017-03-29T09:50:30.730

You can use this. There are few errors in your code and that's why you are getting errors while compilation. Please compare your code with the below one and find the places you made mistakes. My comments are inline.

CREATE OR REPLACE PROCEDURE ACTIVITY1
AS  

   fetch_size  pls_integer := 100 ;

   CURSOR gl
   IS
      SELECT prr.EVT_DECLENCHEUR,
             prr.BQ_CDF,
             prr.BQ_DOM,
             prr.NUM_COMMERCANT,
             prr.TYPE_CONTRAT_COM,
             prr.COMMERCANT_SIRET,
             prr.TYPE_PRE_COMP,
             prr.BQ_CDF_ID_EBF,
             prr.BQ_DOM_ID_EBF,
             prr.DAT_TRAITEMENT,
             prr.EVT_TYPE_OPERATION,
             prr.DEV_MT_CRE,
             prr.NBR_DECI_MT_CRE,
             prr.REF_FICHIER_TRAITE,
             prr.NUM_CTC,
             prr.REF_FICHIER_ORIGINE,
             prr.ORIGINE_FLUX,
             prr.NUM_EST_REMISE,
             prr.NUM_REMISE_ORIGINE,
             prr.NUM_MACHINE,
             prr.REF_ARCHIVAGE_REM,
             prr.DAT_REMISE,
             prr.SUPPORT_REMISE,
             prr.DAT_REMISE_CALCULEE,
             prr.DAT_VALEUR_REMISE,
             prr.COD_APPLI,
             TO_DATE (SYSDATE, 'DD/MM/YYYY')
        FROM dlc_pr_remise prr
       WHERE NOT EXISTS
                (SELECT *
                   FROM DLC_PR_ACTIVITY_REMISE prao
                  WHERE prao.num_est_remise = prr.num_est_remise);

   TYPE GL_T IS TABLE OF gl%ROWTYPE;

   gl_array         GL_T;
BEGIN
   OPEN gl;

   LOOP
      FETCH gl BULK COLLECT INTO gl_array LIMIT fetch_size;

      FORALL i IN 1 .. gl_arary.COUNT
         INSERT INTO dlc_pr_activity_remise -- I assume the dlc_pr_activity_remise table has same columns which you are selectig in cursor.
              VALUES gl_arary (i);

      --Exit the loop when you cursor is empty.
      EXIT WHEN gl%NOTFOUND;

   END LOOP;

   COMMIT;

  CLOSE gl;

END ACTIVITY1;

i have 2 errors Error(41,26): PLS-00201: identifier 'GL' must be declared and Error(50,24): PLS-00201: identifier 'GL_ARARY.COUNT' must be declared ! — Anas Rafei, Mar 29 '17 at 09:49
I just copied your code. Cursor name was g1 initially and you were using it later as gl. Run again ..I modified the code — XING, Mar 29 '17 at 09:51
Thnak you very much Xing now it compile with no error, in the procedure that you wrot the commit interval is 100 ?? — Anas Rafei, Mar 29 '17 at 09:56
I hope you want you procedure to be performant. Isn't it ? Now you can read here why I put 100. http://stackoverflow.com/questions/38659739/setting-a-value-for-limit-while-using-bulk-collect — XING, Mar 29 '17 at 09:57
I have understand why you put 100 now thank you very much XING , i hope it wont take 3 days to finish the 100 million row i have :D — Anas Rafei, Mar 29 '17 at 10:03

score 0 · Answer 2 · answered Mar 29 '17 at 08:15

cursor g1 is select acct_nbr from dwc_tmp_acct_rcvbl;

The cursor's select above must be replaced with :

SELECT prr.EVT_DECLENCHEUR ,
prr.BQ_CDF ,
prr.BQ_DOM ,
prr.NUM_COMMERCANT ,
prr.TYPE_CONTRAT_COM ,
prr.COMMERCANT_SIRET ,
prr.TYPE_PRE_COMP ,
prr.BQ_CDF_ID_EBF ,
prr.BQ_DOM_ID_EBF ,
prr.DAT_TRAITEMENT ,
prr.EVT_TYPE_OPERATION ,
prr.DEV_MT_CRE ,
prr.NBR_DECI_MT_CRE ,
prr.REF_FICHIER_TRAITE ,
prr.NUM_CTC ,
prr.REF_FICHIER_ORIGINE ,
prr.ORIGINE_FLUX ,
prr.NUM_EST_REMISE ,
prr.NUM_REMISE_ORIGINE ,
prr.NUM_MACHINE ,
prr.REF_ARCHIVAGE_REM ,
prr.DAT_REMISE ,
prr.SUPPORT_REMISE ,
prr.DAT_REMISE_CALCULEE ,
prr.DAT_VALEUR_REMISE ,
prr.COD_APPLI ,
to_date(SYSDATE,'DD/MM/YYYY')
  FROM dlc_pr_remise prr
  WHERE NOT EXISTS
    (SELECT *
    FROM DLC_PR_ACTIVITY_REMISE prao
    WHERE prao.num_est_remise = prr.num_est_remise
    )

Then , this line :

insert into dwc_tmp_acct_rcvbl_year_col (acct_nbr)

must be replaced with

INSERT INTO dlc_pr_activity_remise values(add all values using ga_array(i).<value in select>)

Hope this helps.

score 0 · Answer 3 · answered Mar 29 '17 at 19:59

0

The increment of uncommitted needs to be uncommitted := uncommitted + sql%rowcount ;

Not as in post uncommitted :+ uncommitted + sql%rowcount ;

answered Mar 29 '17 at 19:59

Belayer

13,578
2
11
22

Insert from one table into another with commit Intervals

3 Answers3