Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6894
  • Last Modified:

How to use bulk insert into stored procedure?

Hello,

Below is a procedure to insert and commit every 10,000 rows.
 
How would I modify this procedure to use bulk insert?

++++++++++++++

PROCEDURE INS_MLER

   IS

    comm_after NUMBER :=10000;
    rnum NUMBER := 0;
    c1 ROWID;
    CURSOR cur1 IS
        SELECT ROWID
        FROM metastorm.mler_old e
        WHERE e.batchnumber in
           (SELECT h.batchnumber
          FROM metastorm.memberloadheader h);         
BEGIN
    OPEN cur1;
    LOOP
        FETCH cur1
            INTO c1;
        EXIT WHEN cur1%NOTFOUND;
        rnum := rnum + 1;
INSERT INTO metastorm.mler
    (batchnumber,
    loadtrackdetailkey,
    mlfinitestateid,
    mlfinitestatedesc,
    mlappliestoallplans,
    mlloadrecordtomem,
    creationdate,
    creationuser,
    creationprogram,
    maintenancedate,
    maintenanceuser,
    maintenanceprogram)
select
    batchnumber,
    loadtrackdetailkey,
    mlfinitestateid,
    mlfinitestatedesc,
    mlappliestoallplans,
    mlloadrecordtomem,
    creationdate,
    creationuser,
    creationprogram,
    maintenancedate,
    maintenanceuser,
    maintenanceprogram
from metastorm.mler_old        
        WHERE ROWID = c1;
        IF MOD(rnum, comm_after) = 0 THEN
            COMMIT;
        END IF;
        END LOOP;
    CLOSE cur1;
    COMMIT;
    dbms_output.put_line('Inserted Into MLRE: Rows ' || to_char(rnum) || '  inserted');  
EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line('ERROR at' || to_char(rnum));
    WHEN OTHERS THEN
        dbms_output.put_line('ERROR at' || to_char(rnum));
END;
0
spinbains
Asked:
spinbains
  • 3
  • 3
  • 2
1 Solution
 
schwertnerCommented:
It is explained here:

http://www.oracle.com/technology/oramag/oracle/04-jan/o14tech_plsql.html

But it has some tradeofs: it uses PGA to create the associated arrys (PL/SQL tables).
0
 
SujithData ArchitectCommented:
How much is the data volume you are inserting here? If it is not too huge,
I would suggest you to try a single insert like the below one. That would be fast.

INSERT INTO metastorm.mler
    (batchnumber,
    loadtrackdetailkey,
    mlfinitestateid,
    mlfinitestatedesc,
    mlappliestoallplans,
    mlloadrecordtomem,
    creationdate,
    creationuser,
    creationprogram,
    maintenancedate,
    maintenanceuser,
    maintenanceprogram) 
select
    batchnumber,
    loadtrackdetailkey,
    mlfinitestateid,
    mlfinitestatedesc,
    mlappliestoallplans,
    mlloadrecordtomem,
    creationdate,
    creationuser,
    creationprogram,
    maintenancedate,
    maintenanceuser,
    maintenanceprogram
from metastorm.mler_old m,
(SELECT distinct h.batchnumber FROM metastorm.memberloadheader h) X
WHERE m.batchnumber = X.batchnumber ;

Open in new window

0
 
SujithData ArchitectCommented:
If you still want to go for the bulk logic an outline of the code would be like this.


declare
 declare the pl/sql tables for each of the columns in the select list.
 declare the cursor as shown in the above post.
begin
 open cursor;
 loop
  fetch cursor into the pl/sql table variables LIMIT 10000;
  exit when <pl/sql table variable 1>.count = 0;
  forall i in 1 .. <pl/sql table variable 1>.count
   insert .....
   values( <pl/sql table variable 1>(i),
           <pl/sql table variable 2>(i),
         .
         .
         );


   commit;
 end loop;

 close cursor;
end;
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
spinbainsAuthor Commented:
MLE_OLD is a large table 134,219,262 rows and 78G segments in size. A single insert/commit would never finish.
 

I don't see how you're using bulk insert in the above code?
0
 
schwertnerCommented:
The Bulk insert is in the line

forall i in 1 .. <pl/sql table variable 1>.count
0
 
spinbainsAuthor Commented:
Here's what I have so far - I forgot to mention this is 9.2.0.7 Enterprise Edition

Here's the code but it's returning error
PLS-00436:implementation restriction:cannot reference fields of BULK In-BIND table of records
PLS-00382:express is of wrong type


PROCEDURE INS_MLE_LOFN7
   IS
    TYPE MLE_TABLE is table of
    memberloadexception%rowtype
    index by
    pls_integer;
        MLE_TAB MLE_TABLE;
        CURSOR CUR1 IS
        SELECT *
        FROM memberloadexception e
        WHERE e.batchnumber in
           (SELECT h.batchnumber
          FROM memberloadheader@lofn7 h);         
BEGIN
    OPEN cur1;
    FETCH cur1 BULK COLLECT INTO MLE_TAB;
    CLOSE CUR1;
FORALL X in MLE_TAB.FIRST..MLE_TAB.LAST          
insert into memberloadexception@lofn7
(batchnumber,
 loadtrackdetailkey,
 plancode,
 benefitlevel,
 enrollmentgroup,
 memberid,
 ml_load_record_to_mem,
 ml_load_trk_dtl_extr_sts1,
 ml_load_trk_dtl_extr_sts2,
 ml_load_trk_dtl_extr_sts3,
 ml_load_trk_dtl_extr_sts4,
 ml_load_trk_dtl_extr_sts5,
 firstname,
 lastname,
 middleinitial,
 addressline1,
 addressline2,
 city,
 state,
 zipcode,
 zipplus4,
 socialsecurity,
 phone,
 nbrofdependants,
 gender,
 birthday,
 enrollmentdate,
    terminationworkdate,
    responsiblememberid,
    membertype,
    insurancecostatus,
    benefiteffectivedate,
    memberterminationdate,
    membertypefromplan,
    membercountreportsts,
    miscmembershipstatus2,
    miscmembershipstatus3,
    memberupdateaction,
    fabspousechildind,
    benefitoption,
    coveragetier,
    locationsortcode,
    creationdate,
    creationuser,
    creationprogram,
    maintenancedate,
    maintenanceuser,
    maintenanceprogram,
    clientcompanycode,
    clientgroupnumber,
    client_reporting_field_1,
    client_reporting_field_2,
    client_reporting_field_3,
    client_reporting_field_4,
    client_reporting_field_5)
VALUES    
   (MLE_TAB(X).batchnumber,
    MLE_TAB(X).loadtrackdetailkey,
    MLE_TAB(X).plancode,
    MLE_TAB(X).benefitlevel,
    MLE_TAB(X).enrollmentgroup,
    MLE_TAB(X).memberid,
    MLE_TAB(X).ml_load_record_to_mem,
    MLE_TAB(X).ml_load_trk_dtl_extr_sts1,
    MLE_TAB(X).ml_load_trk_dtl_extr_sts2,
    MLE_TAB(X).ml_load_trk_dtl_extr_sts3,
    MLE_TAB(X).ml_load_trk_dtl_extr_sts4,
    MLE_TAB(X).ml_load_trk_dtl_extr_sts5,
    MLE_TAB(X).firstname,
    MLE_TAB(X).lastname,
    MLE_TAB(X).middleinitial,
    MLE_TAB(X).addressline1,
    MLE_TAB(X).addressline2,
    MLE_TAB(X).city,
    MLE_TAB(X).state,
    MLE_TAB(X).zipcode,
    MLE_TAB(X).zipplus4,
    MLE_TAB(X).socialsecurity,
    MLE_TAB(X).phone,
    MLE_TAB(X).nbrofdependants,
    MLE_TAB(X).gender,
    MLE_TAB(X).birthday,
    MLE_TAB(X).enrollmentdate,
    MLE_TAB(X).terminationworkdate,
    MLE_TAB(X).responsiblememberid,
    MLE_TAB(X).membertype,
    MLE_TAB(X).insurancecostatus,
    MLE_TAB(X).benefiteffectivedate,
    MLE_TAB(X).memberterminationdate,
    MLE_TAB(X).membertypefromplan,
    MLE_TAB(X).membercountreportsts,
    MLE_TAB(X).miscmembershipstatus2,
    MLE_TAB(X).miscmembershipstatus3,
    MLE_TAB(X).memberupdateaction,
    MLE_TAB(X).fabspousechildind,
    MLE_TAB(X).benefitoption,
    MLE_TAB(X).coveragetier,
    MLE_TAB(X).locationsortcode,
    MLE_TAB(X).creationdate,
    MLE_TAB(X).creationuser,
    MLE_TAB(X).creationprogram,
    MLE_TAB(X).maintenancedate,
    MLE_TAB(X).maintenanceuser,
    MLE_TAB(X).maintenanceprogram,
    MLE_TAB(X).clientcompanycode,
    MLE_TAB(X).clientgroupnumber,
    MLE_TAB(X).client_reporting_field_1,
    MLE_TAB(X).client_reporting_field_2,
    MLE_TAB(X).client_reporting_field_3,
    MLE_TAB(X).client_reporting_field_4,
    MLE_TAB(X).client_reporting_field_5);
    COMMIT;
    dbms_output.put_line('Inserted Into Memberloadexception: Rows ' || to_char(mle_table.COUNT) || ' inserted');  
EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line('ERROR at' || to_char(mle_table));
    WHEN OTHERS THEN
        dbms_output.put_line('ERROR at' || to_char(mle_table));
END;
0
 
SujithData ArchitectCommented:
Unfortunately;
>>    TYPE MLE_TABLE is table of
    memberloadexception%rowtype
    index by
    pls_integer;

This type of declaration is not supported along with bulk operations.

That is why I posted -
>> declare the pl/sql tables for each of the columns in the select list.

Rest of the things are ok, it should work.
0
 
spinbainsAuthor Commented:
I've had some more success. So I'll close the question and award sujith80 the points.

Thanks everyone for your help.

PROCEDURE BULK_COLLECT_INTO_MLER

   IS

  TYPE MLER_BULK_REC IS RECORD
       (R_BATCHNUM MLER_BULK.BATCHNUMBER%TYPE,
        R_LOADTRK  MLER_BULK.LOADTRACKDETAILKEY%TYPE,
        R_STATEID  MLER_BULK.MLFINITESTATEID%TYPE,
        R_STATEDE  MLER_BULK.MLFINITESTATEDESC%TYPE,
        R_ALLPLAN  MLER_BULK.MLAPPLIESTOALLPLANS%TYPE,
        R_RECMEM   MLER_BULK.MLLOADRECORDTOMEM%TYPE,
        R_CRDATE   MLER_BULK.CREATIONDATE%TYPE,
        R_CRUSER   MLER_BULK.CREATIONUSER%TYPE,
        R_CRPGM    MLER_BULK.CREATIONPROGRAM%TYPE,
        R_MDATE    MLER_BULK.MAINTENANCEDATE%TYPE,
        R_MUSER    MLER_BULK.MAINTENANCEUSER%TYPE,
        R_MPGM     MLER_BULK.MAINTENANCEPROGRAM%TYPE);
 
  TYPE MLER_BULK_TAB IS TABLE OF MLER_BULK_REC;    
       
  V_MLER_BULK      MLER_BULK_TAB;
  V_ARRAY_SIZE     NUMBER := 10000;
  V_TOT_CNT        NUMBER(20) :=0;
 
  CURSOR C1 IS
         SELECT
          BATCHNUMBER,
          LOADTRACKDETAILKEY,
          MLFINITESTATEID,
          MLFINITESTATEDESC,
          MLAPPLIESTOALLPLANS,
          MLLOADRECORDTOMEM,
          CREATIONDATE,
          CREATIONUSER,
          CREATIONPROGRAM,
          MAINTENANCEDATE,
          MAINTENANCEUSER,
          MAINTENANCEPROGRAM
         FROM mler_bulk_arch m
           WHERE m.batchnumber IN
           (SELECT h.batchnumber FROM MLHEADER_ARCH h);
           
         
  PROCEDURE writelog(pMSG IN VARCHAR2)
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
 
  BEGIN -- writelog
    INSERT INTO POST_BULK_MLER_LOG
           VALUES ('BULK_COLLECT_INS_MLER','MAIN',pMsg, SYSDATE, 0);
    COMMIT;
  END writelog;
 
  BEGIN
    writelog('Started');
   
  OPEN C1;
  LOOP
    FETCH C1 BULK COLLECT
          INTO
              V_MLER_BULK LIMIT V_ARRAY_SIZE;
         
     INSERT INTO mler_bulk
    (BATCHNUMBER,
     LOADTRACKDETAILKEY,
     MLFINITESTATEID,
     MLFINITESTATEDESC,
     MLAPPLIESTOALLPLANS,
     MLLOADRECORDTOMEM,
     CREATIONDATE,
     CREATIONUSER,
     CREATIONPROGRAM,
     MAINTENANCEDATE,
     MAINTENANCEUSER,
     MAINTENANCEPROGRAM)
     SELECT    
     BATCHNUMBER,
     LOADTRACKDETAILKEY,
     MLFINITESTATEID,
     MLFINITESTATEDESC,
     MLAPPLIESTOALLPLANS,
     MLLOADRECORDTOMEM,
     CREATIONDATE,
     CREATIONUSER,
     CREATIONPROGRAM,
     MAINTENANCEDATE,
     MAINTENANCEUSER,
     MAINTENANCEPROGRAM
     FROM mler_bulk_arch;
               
         
  EXIT WHEN V_MLER_BULK.COUNT=0;
     
  V_TOT_CNT:=V_TOT_CNT+V_MLER_BULK.COUNT;
 
  COMMIT;
 
  writelog('Inserted into MLER_BULK so far: '|| V_TOT_CNT);
  END LOOP;
 
  CLOSE C1;
 
  COMMIT;
 
  writelog('Finished - Total Rows: '|| V_TOT_CNT);
 
EXCEPTION
  WHEN OTHERS THEN
    writelog(SQLERRM);
END;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now