Solved

How to use bulk insert into stored procedure?

Posted on 2008-10-09
8
6,075 Views
Last Modified: 2013-12-19
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
Comment
Question by:spinbains
  • 3
  • 3
  • 2
8 Comments
 
LVL 47

Expert Comment

by:schwertner
ID: 22685460
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
 
LVL 27

Expert Comment

by:sujith80
ID: 22685561
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
 
LVL 27

Expert Comment

by:sujith80
ID: 22685577
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:spinbains
ID: 22686160
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
 
LVL 47

Expert Comment

by:schwertner
ID: 22687424
The Bulk insert is in the line

forall i in 1 .. <pl/sql table variable 1>.count
0
 

Author Comment

by:spinbains
ID: 22690828
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
 
LVL 27

Accepted Solution

by:
sujith80 earned 250 total points
ID: 22702419
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
 

Author Comment

by:spinbains
ID: 22835844
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question