Solved

How can I apply LIMIT to this Block?

Posted on 2010-11-26
2
245 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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility

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

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

744 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

16 Experts available now in Live!

Get 1:1 Help Now