Solved

How to use bulk insert into stored procedure?

Posted on 2008-10-09
8
6,192 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 48

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 48

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

713 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