1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97
|
CREATE OR REPLACE PROCEDURE FACTS.proc_fact_moc_mtn AS
--DECLARE
msg varchar(10000);
p1 varchar(100);
ecode NUMBER;
emesg VARCHAR2(10000);
--TYPE TYP_TAB_EMP IS TABLE OF EMP%Rowtype ;
type type_tab_fact_moc is table of fact_moc%rowtype ;
v_inst_fact_moc type_tab_fact_moc ;
Cursor get_data_from_dblink IS
select /*+ parallel(3) */
msisdn,
trafic_date,
destination,
traffic_type,
vas_service,
account_id,
total_calls,
total_revenu_appel,
total_duree_appel,
total_volume,
apn_info,
extension_code,
service_class_id,
location_number,
service_offerings,
cell_identity,
country_code
from v_fact_moc_mtn@DWS.CG.NET ;
BEGIN
----first insertion MTN to MTN
msg := 'Start first insertion of MTN to MTN traffic in FACT table';
insert into facts_data_log values('v_fact_moc_mtn',sysdate,msg,0);
commit;
Open get_data_from_dblink;
loop
Fetch get_data_from_dblink bulk collect INTO v_inst_fact_moc LIMIT 500;
for i in 1..v_inst_fact_moc.count loop
insert /*+ append */ into fact_moc
(
msisdn,
trafic_date,
destination,
traffic_type,
vas_service,
account_id,
total_calls,
total_revenu_appel,
total_duree_appel,
total_volume,
apn_info,
extension_code,
service_class_id,
location_number,
service_offerings,
cell_identity,
country_code
)
values
(
v_inst_fact_moc(i).msisdn,
v_inst_fact_moc(i).trafic_date,
v_inst_fact_moc(i).destination,
v_inst_fact_moc(i).traffic_type,
v_inst_fact_moc(i).vas_service,
v_inst_fact_moc(i).account_id,
v_inst_fact_moc(i).total_calls,
v_inst_fact_moc(i).total_revenu_appel,
v_inst_fact_moc(i).total_duree_appel,
v_inst_fact_moc(i).total_volume,
v_inst_fact_moc(i).apn_info,
v_inst_fact_moc(i).extension_code,
v_inst_fact_moc(i).service_class_id,
v_inst_fact_moc(i).location_number,
v_inst_fact_moc(i).service_offerings,
v_inst_fact_moc(i).cell_identity,
v_inst_fact_moc(i).country_code
);
exit when get_data_from_dblink%notfound;
commit;
end loop;
end loop ;
msg := 'End of first insertion of MTN to MTN traffic in FACT table';
insert into facts_data_log values('v_fact_moc_mtn', sysdate, msg, 0);
commit;
EXCEPTION WHEN OTHERS THEN
ecode := SQLCODE;
emesg := SQLERRM;
ROLLBACK;
msg :='An error has occured during the insertion in FACT_MOC from v_fact_moc_mtn ';
msg := msg ||': '||TO_CHAR(ecode) || '-' || emesg ;
insert into facts_data_log values('v_fact_moc_mtn',sysdate,msg,0);
commit;
end ;
/ |
Partager