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
| CREATE OR REPLACE PROCEDURE facts.proc_fact_moc IS
DynamicSql varchar2(10000);
dateAndTime varchar(10000);
msg varchar(10000);
p1 varchar(100);
ecode NUMBER;
emesg VARCHAR2(10000);
BEGIN
select 'D'||to_char(sysdate -2,'yyyymmdd')into p1 from dual;
BEGIN
----first insertion
msg := 'Start first insertion in FACT table';
insert into facts_data_log values('v_fact_moc',sysdate,msg,0);
commit;
DynamicSql:=' insert /*+ append */ into fact_moc partition ('||p1||')
(
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
)
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@DWS.cg.NET';
execute immediate(DynamicSql);
commit;
msg := 'End of first insertion in FACT table';
insert into facts_data_log values('v_fact_moc', 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',sysdate,msg,0);
commit;
END ;
END proc_fact_moc ; |
Partager