Solved

How can I apply LIMIT to this Block?

Posted on 2010-11-26
2
256 Views
Last Modified: 2012-05-10
Hi All,

    The following script works fine for small number of records like upto 1 million records.
 
     when I try to load for 20 million records, the process ran out of memory error is occured.

      How can I apply LIMIT to this statement?
 
      and How can I implement cursor to this logic?

      Please Help me.
 
declare
      
  start_time  number;
  end_time   number;
 
  type T_MSISDN is table of TO2_MOBILE_DETAIL_STG.MSISDN%type;
  type T_ACTIVE is table of TO2_MOBILE_DETAIL_STG.ACTIVE%type;
  type T_ICCID is table of  TO2_MOBILE_DETAIL_STG.ICCID%type;
  type T_CUSTOMER_ID is table of TO2_MOBILE_DETAIL_STG.CUSTOMER_ID%type;
  type T_IS_PREPAID is table of TO2_MOBILE_DETAIL_STG.IS_PREPAID%type;
  type T_IMSI is table of TO2_MOBILE_DETAIL_STG.IMSI%type;
  type T_CONTRACT_ID is table of TO2_MOBILE_DETAIL_STG.CONTRACT_ID%type;

  L_MSISDN T_MSISDN;
  L_ACTIVE T_ACTIVE;
  L_ICCID T_ICCID;
  L_CUSTOMER_ID T_CUSTOMER_ID;
  L_IS_PREPAID T_IS_PREPAID;
  L_IMSI T_IMSI;
  L_CONTRACT_ID T_CONTRACT_ID;
 
begin

      start_time := DBMS_UTILITY.get_time;
      
  select MSISDN,
             ACTIVE,
             ICCID,
             CUSTOMER_ID,
             IS_PREPAID,
             IMSI,
             CONTRACT_ID
 bulk collect into
             L_MSISDN,
             L_ACTIVE,
             L_ICCID,
             L_CUSTOMER_ID,
             L_IS_PREPAID,
             L_IMSI,
             L_CONTRACT_ID
                        
      from TO2_MOBILE_DETAIL_STG;

  FORALL i in L_MSISDN.first .. L_MSISDN.last
 
    insert into to2_mobile_detail(MSISDN,ACTIVE,ICCID,CUSTOMER_ID,IS_PREPAID,IMSI,CONTRACT_ID)
      values
      (l_msisdn(i),l_active(i),l_iccid(i),TO_NUMBER(l_customer_id(i)),l_is_prepaid(i),l_imsi(i),TO_NUMBER(l_contract_id(i)));
      
    --dbms_output.put_line(L_MSISDN(i) || ', ' || L_ACTIVE(i));

            
            end_time := DBMS_UTILITY.get_time;
            DBMS_OUTPUT.PUT_LINE( ' Time took ' || to_char((end_time-start_time)/60));
 
end;
/

Thanks & Regards,
Ravi Ch.
0
Comment
Question by:Ravi_Chintada
2 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34221057
hmmm, why don't you do a straight INSERT INTO ... SELECT  for this "migration" ?

apart from that , put a WHERE ROWNUM <= 10000 to limit to 10000 rows in the SELECT statement ...
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 500 total points
ID: 34222032

I agree with angelIII.

BUT if you insist on BULK COLLECT, try something like this:

DECLARE
   start_time      NUMBER;
   end_time        NUMBER;

   TYPE T_MSISDN IS TABLE OF TO2_MOBILE_DETAIL_STG.MSISDN%TYPE;
   TYPE T_ACTIVE IS TABLE OF TO2_MOBILE_DETAIL_STG.ACTIVE%TYPE;
   TYPE T_ICCID IS TABLE OF TO2_MOBILE_DETAIL_STG.ICCID%TYPE;
   TYPE T_CUSTOMER_ID IS TABLE OF TO2_MOBILE_DETAIL_STG.CUSTOMER_ID%TYPE;
   TYPE T_IS_PREPAID IS TABLE OF TO2_MOBILE_DETAIL_STG.IS_PREPAID%TYPE;
   TYPE T_IMSI IS TABLE OF TO2_MOBILE_DETAIL_STG.IMSI%TYPE;
   TYPE T_CONTRACT_ID IS TABLE OF TO2_MOBILE_DETAIL_STG.CONTRACT_ID%TYPE;

   L_MSISDN        T_MSISDN;
   L_ACTIVE        T_ACTIVE;
   L_ICCID         T_ICCID;
   L_CUSTOMER_ID   T_CUSTOMER_ID;
   L_IS_PREPAID    T_IS_PREPAID;
   L_IMSI          T_IMSI;
   L_CONTRACT_ID   T_CONTRACT_ID;

   Mycsr           SYS_REFCURSOR;

   V_limit         PLS_INTEGER := 500000;

BEGIN
   start_time := DBMS_UTILITY.get_time;

   OPEN mycsr FOR
      SELECT MSISDN,
             ACTIVE,
             ICCID,
             CUSTOMER_ID,
             IS_PREPAID,
             IMSI,
             CONTRACT_ID
        FROM TO2_MOBILE_DETAIL_STG;

   LOOP
      FETCH mycsr
      BULK COLLECT INTO L_MSISDN,
                        L_ACTIVE,
                        L_ICCID,
                        L_CUSTOMER_ID,
                        L_IS_PREPAID,
                        L_IMSI,
                        L_CONTRACT_ID
      LIMIT v_limit;


      FORALL i IN L_MSISDN.FIRST .. L_MSISDN.LAST
         INSERT INTO to2_mobile_detail (MSISDN,
                                        ACTIVE,
                                        ICCID,
                                        CUSTOMER_ID,
                                        IS_PREPAID,
                                        IMSI,
                                        CONTRACT_ID)
             VALUES (l_msisdn (i),
                     l_active (i),
                     l_iccid (i),
                     TO_NUMBER (l_customer_id (i)),
                     l_is_prepaid (i),
                     l_imsi (i),
                     TO_NUMBER (l_contract_id (i)));

      --dbms_output.put_line(L_MSISDN(i) || ', ' || L_ACTIVE(i));

      EXIT WHEN mycsr%NOTFOUND;
   END LOOP;

   end_time := DBMS_UTILITY.get_time;
   DBMS_OUTPUT.PUT_LINE (
      ' Time took ' || TO_CHAR ( (end_time - start_time) / 60));
END;
/

Open in new window

0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PL/SQL Two changes 7 41
Oracle encryption 12 60
oracle date format checking 7 33
Read XML values 8 56
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

749 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