Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to use bulk insert into stored procedure?

Posted on 2008-10-09
8
Medium Priority
?
6,501 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 750 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

Independent Software Vendors: 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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

705 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