Ravi_Chintada
asked on
How can I apply LIMIT to this Block?
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.MSIS DN%type;
type T_ACTIVE is table of TO2_MOBILE_DETAIL_STG.ACTI VE%type;
type T_ICCID is table of TO2_MOBILE_DETAIL_STG.ICCI D%type;
type T_CUSTOMER_ID is table of TO2_MOBILE_DETAIL_STG.CUST OMER_ID%ty pe;
type T_IS_PREPAID is table of TO2_MOBILE_DETAIL_STG.IS_P REPAID%typ e;
type T_IMSI is table of TO2_MOBILE_DETAIL_STG.IMSI %type;
type T_CONTRACT_ID is table of TO2_MOBILE_DETAIL_STG.CONT RACT_ID%ty pe;
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,A CTIVE,ICCI D,CUSTOMER _ID,IS_PRE PAID,IMSI, CONTRACT_I D)
values
(l_msisdn(i),l_active(i),l _iccid(i), TO_NUMBER( l_customer _id(i)),l_ is_prepaid (i),l_imsi (i),TO_NUM BER(l_cont ract_id(i) ));
--dbms_output.put_line(L_M SISDN(i) || ', ' || L_ACTIVE(i));
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE( ' Time took ' || to_char((end_time-start_ti me)/60));
end;
/
Thanks & Regards,
Ravi Ch.
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.MSIS
type T_ACTIVE is table of TO2_MOBILE_DETAIL_STG.ACTI
type T_ICCID is table of TO2_MOBILE_DETAIL_STG.ICCI
type T_CUSTOMER_ID is table of TO2_MOBILE_DETAIL_STG.CUST
type T_IS_PREPAID is table of TO2_MOBILE_DETAIL_STG.IS_P
type T_IMSI is table of TO2_MOBILE_DETAIL_STG.IMSI
type T_CONTRACT_ID is table of TO2_MOBILE_DETAIL_STG.CONT
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,A
values
(l_msisdn(i),l_active(i),l
--dbms_output.put_line(L_M
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE( ' Time took ' || to_char((end_time-start_ti
end;
/
Thanks & Regards,
Ravi Ch.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
apart from that , put a WHERE ROWNUM <= 10000 to limit to 10000 rows in the SELECT statement ...