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
Solved

How can I apply LIMIT to this Block?

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Get the parent node - XMLTYPE 9 97
Oracle Public Synonyms and Privileges 2 73
sum of columns in a row in oracle 3 32
update using pipeline function 3 20
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

808 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