Solved

How can I apply LIMIT to this Block?

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup
Suggested Courses

615 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