Solved

How to use bulk insert into stored procedure?

Posted on 2008-10-09
8
6,005 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:spinbains
Comment Utility
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
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.

 
LVL 47

Expert Comment

by:schwertner
Comment Utility
The Bulk insert is in the line

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

Author Comment

by:spinbains
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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 shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now