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

x
?
Solved

How can I apply LIMIT to this Block?

Posted on 2010-11-26
2
Medium Priority
?
270 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
[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
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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

730 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