Update script mit Tabelle pl/sql parallel
Es Skript, wo mit Hilfe vonere Tabälle, wo erstellt und droped wird via pl/sql script (generiert us Avaloq Script) parallel Adrässdate duet update
/*
-------------------------------------------------------------------------------------------------------------
Bank:
Business Area:
Description:
-------------------------------------------------------------------------------------------------------------
ID Date Name, Company Issue Affected BUs, Description
-------------------------------------------------------------------------------------------------------------
001
-------------------------------------------------------------------------------------------------------------
*/
--- Create table and index for check_email
CREATE TABLE K.CHECK_EMAIL
(
OBJ_ID NUMBER(9),
KEY_VAL VARCHAR2(60 BYTE),
EL_ADDR VARCHAR2(200 BYTE),
PUBL_UNDES VARCHAR2(60 BYTE),
SOURCE VARCHAR2(60 BYTE),
BROCHURES VARCHAR2(200 BYTE),
ADDR_ROLE VARCHAR2(60 BYTE)
)
TABLESPACE U1
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE INDEX K.CHECK_EMAIL#I#1 ON K.CHECK_EMAIL
(KEY_VAL, OBJ_ID)
LOGGING
TABLESPACE I2
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 16K
NEXT 8M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
---------------------------------------------------------------------------------------------------------------------------
--- insert into check email according insert_statements_check_email new spec.sql
Time: 6 minutes
---------------------------------------------------------------------------------------------------------------------------
--- install procedure addr_email_role
create or replace procedure k.addr_email_role(i_obj_id number)
is
l_obj_id varchar2(2000) := i_obj_id;
begin
session#.open_session(i_bu_id => 6);
doc_mgr#.reset(null);
declare
i#l#10 varchar2(1);
begin
declare
f#13# varchar2(1);
begin
f#13# := iff(obj#.obj#has_lck(l_obj_id), '+', '-');
i#l#10 := f#13#;
end;
if i#l#10 = '-' then
declare
f#40# doc_mgr#.t_doc; f#40#1 pls_integer;
begin
f#40#1 := ld_lib_2.cnv_code('wfc_action', base_par#.base_par#id_sql('avq.doc.addr', 'wfc_modify', null, null, null, null),
'user_id,intl_id,id', 'meta_typ_id = def_meta_typ.addr');
f#40# := doc_mgr#.stack#next_doc;
doc_addr#.doc#new(f#40#);
doc_addr#.doc#do_wfc_action(f#40#, i_wfc_action_id => f#40#1, i_is_origin => '+' = '-', i_addr_id => l_obj_id);
declare
id#19 varchar2(2000) := f#40#;
begin
doc_tab_addr#.addr#set_addr_role_id(doc_tab_addr#.doc#addr(id#19, doc_addr#.c_addr), 5004);
declare
f#29# doc_tab_postit#.t_postit;
begin
f#29# := doc_tab_postit#.doc#add_postit(
i_doc => id#19
,i_topic => 'Added address role: Authenticated - CRQ 20140820-0354'
,i_text => null
,i_postit_type_id => nvl(null,null)
,i_postit_sub_type_id => nvl(null,null)
,i_role_id => null
,i_copy_to => '-' = '+'
);
end;
declare
f#37# pls_integer;
begin
f#37# := ld_lib_2.cnv_code('wfc_action', base_par#.base_par#id_sql('avq.doc.addr', 'wfc_verify', null, null, null, null)
, 'user_id,intl_id,id', 'meta_typ_id = def_meta_typ.addr');
doc_addr#.doc#do_wfc_action(id#19, i_wfc_action_id => f#37#, i_is_origin => '-' = '-' , i_addr_id => null);
end;
exception
when others then
raise_fa_err('script with block (8)');
end;
end;
else
install#.log#write(l_obj_id || ': Address is locked');
end if;
end;
end;
/
---------------------------------------------------------------------------------------------------------------------------
--- execute update of addr_email_role parallel
-- Time: 6 Minutes
declare
c_max_slaves pls_integer := 20;
l_master_id pls_integer;
l_sql_stmt varchar2(32000);
l_num_slaves pls_integer;
begin
session#.open_session;
install#.log#write('--- Update Adress Email roles', 0);
install#.log#write('STARTING MASTER AND '||l_num_slaves||' SLAVES...');
install#.master#start_slave(i_wait=> true, i_cnt => l_num_slaves, x_master_id => l_master_id);
for c in (
select obj_id
from obj_addr_hist
where 1=1
and bu_id = 6
and sysdate between date_valid and date_valid_to
and addr_type_id = 3 -- electronical
and addr_medium_id = 2 --email
and addr_role_id is null
)
loop
l_sql_stmt := 'begin k.addr_email_role('||c.obj_id||'); end;';
install#.master#send(i_master_id => l_master_id, i_stmt => l_sql_stmt);
end loop;
install#.log#write('ALL JOBS SEND TO MASTER, WAITING FOR FEEDBACK...');
install#.master#feedback_wait(i_master_id => l_master_id, i_all => true);
install#.log#write('ALL JOBS DONE.');
install#.master#stop_slave(i_master_id => l_master_id);
install#.master#reset_master(i_master_id => l_master_id);
install#.log#write('--- Update Adress Email role finished', 0);
exception
when others then
install#.log#write('error: '||sqlerrm);
end;
/
---------------------------------------------------------------------------------------------------------------------------
--- install procedure person_email_update
create or replace procedure k.person_email_update(i_obj_id number
,i_email varchar2
,i_source varchar2
)
is
l_obj_id varchar2(2000) := i_obj_id;
l_email varchar2(4000) := i_email;
l_source varchar2(4000) := i_source;
l_exists boolean := false;
l_publ_undes boolean := false;
l_diary_exists boolean := false;
begin
declare
i#l#15 varchar2(1);
begin
session#.open_session(i_bu_id => 6);
doc_mgr#.reset(null);
declare
f#18# varchar2(1);
begin
f#18# := iff(obj#.obj#has_lck(l_obj_id), '+', '-');
i#l#15 := f#18#;
end;
if i#l#15 = '-' then
declare
f#138# doc_mgr#.t_doc;
f#138#1 pls_integer;
begin
f#138#1 := ld_lib_2.cnv_code('wfc_action'
,base_par#.base_par#id_sql(
'avq.doc.person'
,'wfc_modify'
,session#.session#bu_id
,null
,null
,null
)
,'user_id,intl_id,id'
,'meta_typ_id = def_meta_typ.person'
);
f#138# := doc_mgr#.stack#next_doc;
doc_person#.doc#new(f#138#);
doc_person#.doc#do_wfc_action(f#138#
,i_wfc_action_id => f#138#1
,i_is_origin => '+' = '-'
,i_obj_id => l_obj_id
);
declare
id#24 varchar2(2000) := f#138#;
begin
if doc_tab_obj_add#.add#id(doc_tab_obj_add#.doc#add(id#24, 8519))
is not null then
l_publ_undes := true;
end if;
declare
f#37##t def.tab_char2000;
begin
f#37##t.delete;
for i in 1 .. doc_tab_addr#.doc#addr_cnt(id#24) loop
f#37##t(f#37##t.count + 1) := (doc_tab_addr#.doc#addr(id#24, i));
end loop;
for i37 in 1 .. f#37##t.count loop
begin
if doc_tab_addr#.addr#addr_medium_id(f#37##t(i37)) = 2 then
if lower(trim(l_email)) =
lower(trim(doc_tab_addr#.addr#el_addr(f#37##t(i37)))) then
l_exists := true;
if l_source = 'diary' then
doc_tab_addr#.addr#set_addr_role_id(f#37##t(i37), 5005);
end if;
end if;
end if;
exception
when others then
raise_fa_err('script for loop (19)');
end;
end loop;
end;
if not l_exists then
declare
f#110# doc_tab_addr#.t_addr;
begin
f#110# := doc_tab_addr#.doc#add_addr(id#24);
declare
id#68 varchar2(2000) := f#110#;
begin
doc_tab_addr#.addr#set_addr_type_id(id#68, 3);
doc_tab_addr#.addr#set_addr_medium_id(id#68, 2);
doc_tab_addr#.addr#set_el_addr(id#68, l_email);
if l_source = 'diary' then
doc_tab_addr#.addr#set_addr_role_id(id#68, 5003);
doc_tab_addr#.addr#set_remark(id#68, '||SRC: 2015 diary||');
else
if l_source = 'survey' then
doc_tab_addr#.addr#set_remark(id#68, '||SRC: 2015 client survey||');
end if;
end if;
exception
when others then raise_fa_err('script with block (35)');
end;
end;
end if;
if l_source = 'diary' then
declare
f#107##t def.tab_char2000;
begin
f#107##t.delete;
for i in 1 .. doc_tab_obj_add#.add#val_cnt(doc_tab_obj_add#.doc#add(id#24, 8526)) loop
f#107##t(f#107##t.count + 1) := (doc_tab_obj_add#.add#id(
doc_tab_obj_add#.doc#add(
id#24
,8526
)
,i
));
end loop;
for i107 in 1 .. f#107##t.count loop
begin
if f#107##t(i107) = 6176 then
l_diary_exists := true;
end if;
exception
when others then
raise_fa_err('script for loop (45)');
end;
end loop;
if not l_diary_exists then
declare
f#123# pls_integer;
begin
f#123# := doc_tab_obj_add#.add#val_cnt(
doc_tab_obj_add#.doc#add(id#24, 8526)
)
+ 1;
doc_tab_obj_add#.add#set_id(
doc_tab_obj_add#.doc#add(id#24, 8526)
,6176
,f#123#
);
end;
end if;
end;
end if;
declare
f#130# doc_tab_postit#.t_postit;
begin
f#130# := doc_tab_postit#.doc#add_postit(
i_doc => id#24
,i_topic => 'Sourced from '
|| l_source
|| ' - CRQ 20140820-0354'
,i_text => null
,i_postit_type_id => nvl(null, null)
,i_postit_sub_type_id => nvl(null, null)
,i_role_id => null
,i_copy_to => '-' = '+'
);
end;
declare
f#135# pls_integer;
begin
f#135# := ld_lib_2.cnv_code(
'wfc_action'
,1080
,'user_id,intl_id,id'
,'meta_typ_id = def_meta_typ.person'
);
doc_person#.doc#do_wfc_action(id#24
,i_wfc_action_id => f#135#
,i_is_origin => '-' = '-'
,i_obj_id => null
);
end;
exception
when others then
raise_fa_err('script with block (13)');
end;
end;
else
install#.log#write(l_obj_id || ': Person is locked');
end if;
end;
exception
when others then
install#.log#write('failed to update person: ' || l_obj_id);
end;
/
---------------------------------------------------------------------------------------------------------------------------
--- execute update of person_email_update parallel
-- Time: 25 Minutes
declare
c_max_slaves pls_integer := 20;
l_master_id pls_integer;
l_sql_stmt varchar2(32000);
l_num_slaves pls_integer;
begin
session#.open_session;
install#.log#write('--- Update Person Email addresses', 0);
install#.log#write('STARTING MASTER AND '||l_num_slaves||' SLAVES...');
install#.master#start_slave(i_wait=> true, i_cnt => l_num_slaves, x_master_id => l_master_id);
for c in (
select *
from check_email
)
loop
l_sql_stmt := 'begin k.person_email_update('||c.obj_id||','''||c.el_addr||''','''||c.source||'''); end;';
install#.master#send(i_master_id => l_master_id, i_stmt => l_sql_stmt);
end loop;
install#.log#write('ALL JOBS SEND TO MASTER, WAITING FOR FEEDBACK...');
install#.master#feedback_wait(i_master_id => l_master_id, i_all => true);
install#.log#write('ALL JOBS DONE.');
install#.master#stop_slave(i_master_id => l_master_id);
install#.master#reset_master(i_master_id => l_master_id);
install#.log#write('--- Update Adress Email role finished', 0);
exception
when others then
install#.log#write('error: '||sqlerrm);
end;
/
---------------------------------------------------------------------------------------------------------------------------
--- Drop procedure addr_email_role
drop procedure k.addr_email_role;
---------------------------------------------------------------------------------------------------------------------------
--- Drop procedure person_email_update
drop procedure k.person_email_update
---------------------------------------------------------------------------------------------------------------------------
--- Drop table check_email
drop table k.check_email cascade constraints;
---------------------------------------------------------------------------------------------------------------------------
--- control queries
select oa.addr_role_id, ca.name, count(*)
from obj_addr oa
,code_addr_role ca
where 1=1
and oa.addr_role_id = ca.id
and oa.bu_id = 6
and oa.addr_type_id = 3 -- electronical
and oa.addr_medium_id = 2
group by oa.addr_role_id, ca.name
select max(id) result
from doc
where 1=1
select count(*)
from doc
where 1=1
and id > result
and ins_by_sec_user_id = 310
and meta_typ_id = 101
select count(obj_id)
from obj_addr_hist
where 1=1
and bu_id = 6
and sysdate between date_valid and date_valid_to
and addr_type_id = 3 -- electronical
and addr_medium_id = 2 --email
and addr_role_id is null
with ilog as(
select time_stamp timestamp, trim(regexp_replace(text, '[^a-zA-Z ]')) reason, regexp_replace(text, '[^0-9]+') person_id
from k.install_log
where 1=1
and time_stamp > 'START'
and time_stamp < 'end'
and log_type != 'DEBUG'
and regexp_replace(text, '[^0-9]+') is not null
)
select ilg.*, pek.key_val person_key
from ilog ilg
,obj_rel_key pek
where ilg.person_id = pek.obj_id
and pek.obj_key_id (+) = 806
order by reason, person_id
with ilog_role as(
select substr(text, 1, instr(text, ': ')-1) addr_id, oah.owner_obj_id person_id, orl.key_val key, text
from k.install_log ilg
,k.obj_addr_hist oah
,obj_rel_key orl
where 1=1
and oah.obj_id = substr(text, 1, instr(text, ': ')-1)
and time_stamp >= current_timestamp -6/24
and log_type != 'DEBUG'
and (instr(text, ': Address is locked') > 0
or instr(text, 'script with block') > 0)
and sysdate between oah.date_valid and oah.date_valid_to
and oah.owner_obj_id = orl.obj_id (+)
and orl.obj_key_id (+) = 607
and oah.obj_id = orl.ref_obj_id
)
select ilr.*, pek.key_val person_key
from ilog_role ilr
,obj_rel_key pek
where ilr.person_id = pek.obj_id
and pek.obj_key_id (+) = 806
