API Export - Package body
En Task zum exportiere vom API in e XML
create or replace package body k.rbsc$api_exp# is
/*
-------------------------------------------------------------------------------------------------------------
Bank:
Business Area:
Description: Used to extract the Avaloq API
-------------------------------------------------------------------------------------------------------------
ID Date Name, Company Issue Affected BUs, Description
-------------------------------------------------------------------------------------------------------------
001
-------------------------------------------------------------------------------------------------------------
*/
b_file_name varchar2(200);
b_sub_dir varchar2(200);
b_no_sub_dir boolean;
b_buf buf#.t_buf;
b_dest_path varchar2(100) := 'api_xml';
----------------------------------------------------------------------------
-- Procedure to make sure the required directory strucure exists in AAA_DB_IO
procedure init_api_exp(
i_xml_path varchar2 := null
,i_no_sub_dir boolean := false
)
is
begin
if i_xml_path is null then
b_dest_path := 'api_xml';
else
require(instr(i_xml_path, '..') = 0, 'Relative path to IO directory');
require(instr(i_xml_path, '//') = 0, 'Valid IO sub-directory');
b_dest_path := i_xml_path;
end if;
b_no_sub_dir := i_no_sub_dir;
-- Try removing "dangerous" characters from directory
b_dest_path := rtrim(ltrim(b_dest_path, '/'), '/');
b_dest_path := translate(b_dest_path, ';: \', '____');
for c in (
select directory_path||'/'||b_dest_path dir
from all_directories
where directory_name = 'AAA_DB_IO'
and directory_path is not null
) loop
-- remove existing directory
rbsc_post_clone#.run_os_cmd('rm -r '||c.dir);
end loop;
end init_api_exp;
----------------------------------------------------------------------------
-- Returns the current state as a string
function current_state
return varchar2
is
l_state varchar2(32760);
begin
l_state := lib_co.set_item_val('filename', l_state, b_file_name, i_add=>true);
l_state := lib_co.set_item_val('sub_dir', l_state, b_sub_dir, i_add=>true);
l_state := lib_co.set_item_val('buf', l_state, b_buf, i_add=>true);
l_state := lib_co.set_item_val('dest_path', l_state, b_dest_path, i_add=>true);
b_buf := null;
b_file_name := null;
return l_state;
end current_state;
----------------------------------------------------------------------------
procedure restore_state(
i_state varchar2
)
is
begin
b_file_name := lib_co.get_item_val('filename', i_state);
b_sub_dir := lib_co.get_item_val('sub_dir', i_state);
b_buf := lib_co.get_item_val('buf', i_state);
b_dest_path := lib_co.get_item_val('dest_path', i_state);
end restore_state;
----------------------------------------------------------------------------
-- Create a new buffer, initialize it with the XML header
procedure xml#new(
i_file_name varchar2
,i_sub_dir varchar2
)
is
l_style_sheet_path varchar2(100);
begin
if b_buf is not null then
xml#finalize;
end if;
if b_no_sub_dir then
b_file_name := translate(i_sub_dir, '/', '_')||'_'||i_file_name;
b_sub_dir := null;
else
b_file_name := i_file_name;
b_sub_dir := b_dest_path||'/'||i_sub_dir;
end if;
b_buf := buf#.new;
if i_sub_dir is not null then
l_style_sheet_path := regexp_replace(regexp_replace(b_sub_dir, '([^/]+/)', '../'), '/[^/]*$', '/');
end if;
buf#.buf#write(b_buf, '<?xml version="1.0" encoding="ISO-8859-1"?>'||chr(10)
||'<!DOCTYPE avq_api SYSTEM "'||l_style_sheet_path||'avq_api.dtd">'||chr(10)
||'<?xml-stylesheet type="text/xsl" href="'||l_style_sheet_path||'avq_api.xsl" ?>'||chr(10)
);
exception
when others then
raise_fa_err('xml#new('||i_file_name||','||i_sub_dir||')');
end xml#new;
----------------------------------------------------------------------------
-- Finalize XML by writting the file and remove the buffer
procedure xml#finalize
is
begin
if b_buf is not null then
store_buf(b_file_name, b_sub_dir, b_buf);
buf#.remv(b_buf);
end if;
exception
when others then
raise_fa_err('xml#finalize');
end xml#finalize;
----------------------------------------------------------------------------
procedure xml#write(
i_text varchar2
)
is
begin
buf#.buf#write(b_buf, i_text);
exception
when others then
raise_fa_err('xml#write('||i_text||')');
end xml#write;
----------------------------------------------------------------------------
procedure xml#write(
i_clob clob
)
is
begin
buf#.buf#write(b_buf, i_clob);
exception
when others then
raise_fa_err('xml#write('||i_clob||')');
end xml#write;
----------------------------------------------------------------------------
procedure xml#write_line(
i_text varchar2
)
is
begin
xml#write(rtrim(i_text)||chr(10));
exception
when others then
raise_fa_err('xml#write_line('||i_text||')');
end xml#write_line;
----------------------------------------------------------------------------
function xml#encode(
i_clob clob
) return clob
is
begin
return replace(replace(replace(replace(replace(i_clob, '&', '&'), '"', '"'), '<', '<'), '>', '>'),'''', ''');
exception
when others then
raise_fa_err('xml#encode(i_clob=>'||substr(i_clob, 1, 100)||')', i_clob);
end xml#encode;
----------------------------------------------------------------------------
function xml#encode(
i_text varchar2
) return varchar2
is
begin
return replace(replace(replace(replace(replace(i_text, '&', '&'), '"', '"'), '<', '<'), '>', '>'),'''', ''');
exception
when others then
raise_fa_err('xml#encode('||i_text||')');
end xml#encode;
----------------------------------------------------------------------------
procedure store_buf(
i_file_name varchar2
,i_sub_dir varchar2
,i_buf buf#.t_buf
)
is
procedure send_msg
is
l_msg_rec msg_extl#.t_msg_rec;
l_msg_prty_rec msg_extl#.t_msg_prty_rec;
begin
l_msg_rec.msg := i_buf;
l_msg_rec.prty_list('filename') := i_file_name;
l_msg_rec.prty_list('sub_dir') := i_sub_dir;
l_msg_prty_rec.netw_id := 67;
l_msg_prty_rec.msg_type := null;
l_msg_prty_rec.msg_dlv_type_id := 2;
l_msg_prty_rec.addr := null;
l_msg_prty_rec.consmr := 'amitest_a';
l_msg_prty_rec.prio := 9;
msg_extl#.netw#put_out_msg(
x_msg_prty_rec => l_msg_prty_rec
,i_msg_rec => l_msg_rec
);
end send_msg;
procedure write_file
is
l_file number;
begin
l_file := s#file.new(i_file_type_id=>file_type#.file_type_id('xml_no_aggr'),i_file_name=>replace(i_file_name, '#', '__'));
--l_file := s#file.new(i_file_type_id=>file_type#.file_type_id('xml_no_aggr'),i_file_name=>utl_url.escape(replace(i_file_name, '#', '__'), true));
file#.file#set_trl(l_file, i_sub_dir);
s#file.write_buf(l_file, i_buf);
s#file.write_file(l_file, i_do_autr=>true, i_append=>true);
end write_file;
begin
--send_msg;
write_file;
exception
when others then
raise_fa_err('store_buf(i_file_name=>'||i_file_name||')');
end store_buf;
----------------------------------------------------------------------------
function get_ddic_path(
i_ddic_name varchar2
) return varchar2
is
l_hash_pos number;
begin
l_hash_pos := instr(i_ddic_name, '#');
return 'ddic/'||substr(lower(i_ddic_name),1,1)||'/'
||case when i_ddic_name like 'mem_msg_%' then 'mem_msg/'
||case when l_hash_pos > 0 then
regexp_replace(substr(i_ddic_name, 9, l_hash_pos-9), '(grp|bdl)_(envelope|aggr)_', '')
||'/'
end
end;
exception
when others then
raise_fa_err('get_ddic_path(i_ddic_name=>'||i_ddic_name||')');
end get_ddic_path;
----------------------------------------------------------------------------
procedure move_files_to_dir
is
l_io_dir varchar2(2000);
--
l_exists boolean;
l_file_length number;
l_blocksize number;
--
l_file file#.t_file;
begin
select directory_path||'/'
into l_io_dir
from all_directories
where directory_name = 'AAA_DB_IO'
and directory_path is not null;
--
assert(l_io_dir is not null and l_io_dir like '%aaa/io/', 'invalid AAA_DB_IO: '||l_io_dir);
--
for c in (
select distinct rtrim(trl, '/') dir
from file_idx
where file_type_id in (5113, 60 /*viz*/)
and trl like b_dest_path||'/%'
) loop
rbsc_post_clone#.run_os_cmd('/bin/mkdir -p '||l_io_dir||c.dir);
execute immediate 'create or replace directory RBSC_API_XML as '''||l_io_dir||c.dir||'''';
for f in (
select *
from file_idx
where file_type_id in (5113, 60 /*viz*/)
and rtrim(trl, '/') = c.dir
) loop
-- Check if source file exists
utl_file.fgetattr('AAA_DB_IO', f.filename, l_exists, l_file_length, l_blocksize);
if l_exists then
-- Move to target directory
utl_file.frename('AAA_DB_IO', f.filename, 'RBSC_API_XML', f.filename, true);
else
install#.log#write('File does not exist: '||f.filename||' (trl='||f.trl||')');
end if;
-- Delete subdirectory from file_idx to mark it a "processed"
l_file := file#.file(f.id);
file#.file#set_trl(l_file, null);
file#.file#store_file_idx(l_file);
end loop;
execute immediate 'drop directory RBSC_API_XML';
end loop;
end move_files_to_dir;
----------------------------------------------------------------------------
procedure gen_base_files
is
begin
xml#new('00_index.xml');
xml#write_line('<main_index/>');
xml#finalize;
commit;
xml#new('01_allmodules.xml');
xml#write_line('<allmodules/>');
xml#finalize;
commit;
exception
when others then
raise_fa_err('gen_ddiclist');
end gen_base_files;
----------------------------------------------------------------------------
procedure gen_ddic(
i_script_tab_id number
,i_script_tab_name varchar2
,i_dest_path varchar2
)
is
l_task_def_id number;
l_task_layout_id number;
l_task_exec_id task_exec#.t_task_exec;
begin
install#.log#write('Processing '||i_script_tab_name||' ('||i_script_tab_id||')...');
session#.open_session;
b_dest_path := i_dest_path;
l_task_def_id := lookup_ddic#.task_def_id('TASK_SCRIPT_DDIC');
l_task_layout_id := lookup_ddic#.task_layout_id('task_script_ddic.rbsc$xml', i_layout_type_id => def_layout_type.script);
l_task_exec_id := task_exec#.task_exec#new(i_task_def_id => l_task_def_id, i_task_templ_id => null, i_task_descn => b_dest_path||'/');
--
task_exec#.task_exec#set_val(
i_task_exec => l_task_exec_id
,i_par_id => task#.task_def#par_id(l_task_def_id, 'i_layout_id')
,i_val_num => l_task_layout_id
);
--
task_exec#.task_exec#set_val(
i_task_exec => l_task_exec_id
,i_par_id => task#.task_def#par_id(l_task_def_id, 'i_script_tab_id')
,i_val_num => i_script_tab_id
);
--
task_exec#.run(
i_task_def_id => l_task_def_id
,i_templ_id => null
,i_val_tab => task_exec#.task_exec#val_tab(l_task_exec_id)
,i_buf => task_exec#.task_exec#buf(l_task_exec_id)
,i_task_id => null
,i_task_descn => b_dest_path||'/'
,i_doc => null
);
--
task_exec#.task_exec#remv(l_task_exec_id);
exception
when others then
raise_fa_err('gen_ddic('||i_script_tab_name||')');
end gen_ddic;
----------------------------------------------------------------------------
procedure gen_all_ddics
is
begin
rbsc$prl_sql#.start_slaves(40,'Generating DDICs');
for c in (
select t.id, t.name intl_id, s.name src_name, s.src_type_id, cst.user_id src_type
from meta_script_tab t
,src s
,code_src_type cst
where 1=1
and t.activ = '+'
--and exists (select 1 from meta_script_fld where script_tab_id = t.id and activ is not null)
and nvl(s.src_type_id, -1) not in (254 /*rep_dtm*/)
and s.id = t.gen_src_id
and cst.id = s.src_type_id
and t.name not like '##%' -- exclude Helper DDICs (introduced in release 4.1?)
--and s.name like '%CODE%'
order by intl_id
) loop
rbsc$prl_sql#.send_stmt('begin k.rbsc$api_exp#.gen_ddic('||c.id||', '''||c.intl_id||''', '''||b_dest_path||'''); end;');
end loop;
rbsc$prl_sql#.wait_feedback();
rbsc$prl_sql#.stop_slaves();
exception
when others then
rbsc$prl_sql#.stop_slaves();
raise_fa_err('gen_all_ddics');
end gen_all_ddics;
----------------------------------------------------------------------------
procedure gen_ddiclist
is
begin
xml#new('02_allddics.xml');
xml#write_line('<ddiclist>');
for c in (
select t.id, t.name intl_id, s.name src_name, s.src_type_id, cst.user_id src_type
,' <ddicref id="'||t.id||'" name="'||t.name||'" path="'||get_ddic_path(t.name)||'"/>' xml
from meta_script_tab t
,src s
,code_src_type cst
where 1=1
and t.activ = '+'
and exists (select 1 from meta_script_fld where script_tab_id = t.id and activ is not null and name != '##root##')
and nvl(s.src_type_id, -1) not in (254 /*rep_dtm*/)
and s.id = t.gen_src_id
and cst.id = s.src_type_id
and t.name not like 'mem_msg%##%' -- ignoring the generated sub-ddics in the ddic overview
and t.owner_tab_id is null
order by t.name
) loop
xml#write_line(c.xml);
end loop;
xml#write_line('</ddiclist>');
xml#finalize;
commit;
exception
when others then
raise_fa_err('gen_ddiclist');
end gen_ddiclist;
----------------------------------------------------------------------------
procedure gen_summary
is
begin
xml#new('02_summary.xml');
xml#write_line('<summary date="'||to_char(sysdate, 'DD.MM.YYYY HH24:MI:SS')||'" instance="'||sys_context('USERENV', 'INSTANCE_NAME')||'">'||chr(10)
||' <releaselist>');
for c in (
-- Release information for summary sheet
select distinct install_clt_id, rtrim(ic.intl_id||','||ic.alias_list,',') alias
,first_value(release) over (partition by install_clt_id order by timestamp_ins desc) release
,first_value(timestamp_ins) over (partition by install_clt_id order by timestamp_ins desc) timestamp_ins
,' <clientrelease clientId="'||ic.id||'" alias="'||rtrim(ic.intl_id||','||ic.alias_list,',')
||'" release="'||first_value(release) over (partition by install_clt_id order by timestamp_ins desc)
||'" installationDate="'||to_char(first_value(timestamp_ins) over (partition by install_clt_id order by timestamp_ins desc), 'DD.MM.YYYY')
||'" />"' xml
from env_release_ins eri
,x.install_clt ic
where 1=1
and eri.install_app_id in (2,3)
and ic.id = eri.install_clt_id
order by eri.install_clt_id, timestamp_ins desc
) loop
xml#write_line(c.xml);
end loop;
xml#write_line(' </releaselist>'||chr(10)
||'</summary>'
);
xml#finalize;
commit;
exception
when others then
raise_fa_err('gen_summary');
end gen_summary;
----------------------------------------------------------------------------
procedure gen_releases
is
begin
xml#new('02_releases.xml');
xml#write_line('<releaselist>');
for c in (
-- Release information for summary sheet
select distinct install_clt_id, rtrim(ic.intl_id||','||ic.alias_list,',') alias
,release release
,timestamp_ins
,' <clientrelease clientId="'||ic.id||'" alias="'||rtrim(ic.intl_id||','||ic.alias_list,',')
||'" release="'||release
||'" installationDate="'||to_char(timestamp_ins, 'DD.MM.YYYY')
||'" />' xml
-- ,eri.*
from env_release_ins eri
,x.install_clt ic
where 1=1
and eri.install_app_id in (2,3)
and eri.install_clt_id in (0, 34)
and ic.id = eri.install_clt_id
order by timestamp_ins desc, eri.install_clt_id
) loop
xml#write_line(c.xml);
end loop;
xml#write_line('</releaselist>');
xml#finalize;
commit;
exception
when others then
raise_fa_err('gen_releases');
end gen_releases;
----------------------------------------------------------------------------
-- extr_raw_src: extract RAW source text to be extracted
procedure extr_raw_src(
i_src_id pls_integer
)
is
l_saved_state varchar2(32760);
begin
l_saved_state := current_state;
for s in (
select 'source/'||replace(cst.user_id, ' ', '_') sub_dir, s.name||'.xml' filename, s.id, s.name, cst.user_id type, ref_id
,(select text from src_hist h where h.src_id = s.id and src_owner_id = 1 and seq_nr = 0) src_avq
,(select text from src_hist h where h.src_id = s.id and src_owner_id = 2 and seq_nr = 0) src_custr
from src s
,code_src_type cst
where 1=1
and s.id = i_src_id
and cst.id = s.src_type_id
) loop
xml#new(s.filename, s.sub_dir);
xml#write('<source id="'||s.id||'" name="'||xml#encode(s.name)||'" type="'||xml#encode(s.type)||'" refId="'||s.ref_id||'">');
if s.src_avq is not null then
xml#write_line('<avaloq>');
xml#write(xml#encode(s.src_avq));
xml#write_line('</avaloq>');
end if;
if s.src_custr is not null then
xml#write_line('<customer>');
xml#write(xml#encode(s.src_custr));
xml#write_line('</customer>');
end if;
xml#write_line('</source>');
xml#finalize;
end loop;
commit;
restore_state(l_saved_state);
exception
when others then
restore_state(l_saved_state);
raise_fa_err('extr_raw_src('||i_src_id||')');
end extr_raw_src;
----------------------------------------------------------------------------
procedure gen_bgps
is
l_has_prcq boolean;
begin
xml#new('02_bgps.xml');
xml#write_line('<bgplist>');
for c in (
-- Find BGPs
select ob.obj_id bgp_id
,get_obj(ob.obj_id) name
,lower(ok.key_val) intl_id
,o.close_date
,case when o.close_date is null or o.close_date > base#.today then 'true' else 'false' end enabled
,cost.user_id type
,su.oracle_user sec_user_init
,s.id src_id, s.name src_name, (select user_id from code_src_owner where id = s.src_owner_id) src_owner
,(select case when count(distinct src_text_type_id) = 2 then 'true' else 'false' end
from src_hist
where 1=1
and src_id = s.id
and seq_nr = 0) has_override
,ob.interval, ob.max_instn_cnt, ob.dflt_instn_cnt, ob.stmt
,ob.session_level_id
from obj_bgp ob
,obj_rel_key ok
,src s
,sec_user su
,obj o
,code_obj_sub_type cost
where 1=1
and o.id (+)= ob.obj_id
and cost.id (+)= o.obj_sub_type_id
and ok.obj_id (+)= ob.obj_id
and ok.obj_key_id (+)= 71
and s.ref_id (+)= ob.obj_id
and s.src_type_id (+)= 205
and su.id (+)= ob.sec_user_init_id
order by 1
) loop
xml#write(' <bgp id="'||c.bgp_id||'" intlId="'||xml#encode(c.intl_id)||'" name="'||xml#encode(c.name)||'" ');
xml#write('type="'||c.type||'" enabled="'||c.enabled||'" ');
if c.sec_user_init is not null then xml#write('initialUser="'||xml#encode(c.sec_user_init)||'" '); end if;
if c.interval is not null then xml#write('interval="'||c.interval||'" '); end if;
if c.max_instn_cnt is not null then xml#write('maximumInstanceCount="'||c.max_instn_cnt||'" '); end if;
if c.dflt_instn_cnt is not null then xml#write('defaultInstanceCount="'||c.dflt_instn_cnt||'" '); end if;
if c.stmt is not null then xml#write('statement="'||xml#encode(c.stmt)||'" '); end if;
if c.session_level_id is not null then xml#write('sessionLevel="'||c.session_level_id||'" '); end if;
xml#write('source="'||xml#encode(c.src_name)||'" sourceOwner="'||xml#encode(c.src_owner)||'" sourceOverriden="'||c.has_override||'" ');
--
-- Find process queues related to BGP
l_has_prcq := false;
for p in (
select rel.obj_2_id prcq_id, get_obj(rel.obj_2_id) prcq_name, lower(ok.key_val) intl_id
,rel.prio
,s#util.item(trl, 1, chr(9)) bu_id
,s#util.item(trl, 2, chr(9)) timeslot
from obj_rel rel
,obj_rel_key ok
where rel.obj_1_id = c.bgp_id
and rel.obj_rel_type_id = 100
and ok.obj_id (+)= rel.obj_2_id
and ok.obj_key_id (+)= 70 -- prcq_sym
order by rel.obj_1_id, prio
) loop
if not l_has_prcq then
l_has_prcq := true;
xml#write_line('>');
end if;
xml#write(' <prcqService id="'||p.prcq_id||'" intlId="'||xml#encode(p.intl_id)||'" name="'||xml#encode(p.prcq_name)||'" ');
if p.prio is not null then xml#write('prio="'||p.prio||'" '); end if;
if p.bu_id is not null then xml#write('buId="'||p.bu_id||'" '); end if;
if p.timeslot is not null then xml#write('timeslot="'||p.timeslot||'" '); end if;
xml#write_line('/>');
end loop;
if l_has_prcq then
xml#write_line(' </bgp>');
else
xml#write_line('/>');
end if;
-- Extract corresponding source
extr_raw_src(c.src_id);
end loop;
xml#write_line('</bgplist>');
xml#finalize;
commit;
move_files_to_dir;
exception
when others then
raise_fa_err('gen_bgps');
end gen_bgps;
----------------------------------------------------------------------------
procedure gen_prcqs
is
begin
xml#new('02_prcqs.xml');
xml#write_line('<prcqlist>');
for c in (
select op.obj_id prcq_id, get_obj(op.obj_id) name
,lower(ok.key_val) intl_id
,cost.user_id type
,case when o.close_date is null or o.close_date > base#.today then 'true' else 'false' end enabled
,su.oracle_user sec_user_init
,s.id src_id, s.name src_name, (select user_id from code_src_owner where id = s.src_owner_id) src_owner
,(select case when count(distinct src_text_type_id) = 2 then 'true' else 'false' end
from src_hist
where 1=1
and src_id = s.id
and seq_nr = 0) has_override
,op.keep
,op.retry_interval
,cppdt.intl_id prcq_prc_date_type
--,(select listagg(rel.obj_2_id||':'||lower(okb.key_val), ',') within group (order by rel.obj_2_id) from obj_rel rel, obj_rel_key okb where rel.obj_1_id = op.obj_id and okb.obj_id (+)= rel.obj_2_id and okb.obj_key_id (+)= 71) bgps
from obj_prcq op
,obj_rel_key ok
,obj o
,src s
,sec_user su
,code_prcq_prc_date_type cppdt
,code_obj_sub_type cost
where 1=1
and ok.obj_id (+)= op.obj_id
and ok.obj_key_id (+)= 70 -- prcq_sym
and o.id (+)= op.obj_id
and cost.id (+)= o.obj_sub_type_id
and s.ref_id (+)= op.obj_id
and s.src_type_id (+)= 204 -- PRCQ
and su.id (+)= op.sec_user_init_id
and cppdt.id (+)= op.prc_date_type_id
order by 1, src_owner_id
) loop
xml#write(' <prcq id="'||c.prcq_id||'" intlId="'||xml#encode(c.intl_id)||'" name="'||xml#encode(c.name)||'" ');
xml#write('type="'||c.type||'" enabled="'||c.enabled||'" initialUser="'||xml#encode(c.sec_user_init)||'" keep="'||c.keep||'" ');
xml#write('retryInterval="'||c.retry_interval||'" dateType="'||c.prcq_prc_date_type||'" ');
xml#write_line('source="'||xml#encode(c.src_name)||'" sourceOwner="'||xml#encode(c.src_owner)||'" sourceOverriden="'||c.has_override||'" >');
-- Find process queues related to BGP
for p in (
select rel.obj_2_id bgp_id, get_obj(rel.obj_2_id) bgp_name, lower(ok.key_val) intl_id
,rel.prio
,s#util.item(trl, 1, chr(9)) bu_id
,s#util.item(trl, 2, chr(9)) timeslot
from obj_rel rel
,obj_rel_key ok
where rel.obj_1_id = c.prcq_id
and rel.obj_rel_type_id = -100
and ok.obj_id (+)= rel.obj_2_id
and ok.obj_key_id (+)= 71 -- bgp_sym
order by rel.obj_1_id, prio desc
) loop
xml#write(' <bgpService id="'||p.bgp_id||'" intlId="'||xml#encode(p.intl_id)||'" name="'||xml#encode(p.bgp_name)||'" ');
if p.prio is not null then xml#write('prio="'||p.prio||'" '); end if;
if p.bu_id is not null then xml#write('buId="'||p.bu_id||'" '); end if;
if p.timeslot is not null then xml#write('timeslot="'||p.timeslot||'" '); end if;
xml#write_line('/>');
end loop;
xml#write_line(' </prcq>');
-- Extract corresponding source
extr_raw_src(c.src_id);
end loop;
xml#write_line('</prcqlist>');
xml#finalize;
commit;
move_files_to_dir;
exception
when others then
raise_fa_err('gen_prcqs');
end gen_prcqs;
----------------------------------------------------------------------------
procedure gen_metatypes
is
l_wfc_file file#.t_file;
begin
xml#new('02_metatypes.xml');
xml#write_line('<metatyplist>');
for c in (
-- List of all Meta Types
select nvl(m.base_meta_typ_id, m.id)+m.id/10000 o, m.id meta_typ_id, cf.id form_id, m.base_meta_typ_id
,m.intl_id
,ltrim(m.name,'!') name
,case when m.activ is not null then 'true' else 'false' end active
,base_mt.intl_id base_meta_typ
,cf.intl_id form
,replace(lower(m.intl_id), '#', '__')||'.viz' viz_file
--,cf.intf_def_id
,s.id src_id, s.name src_name, (select user_id from code_src_owner where id = s.src_owner_id) src_owner
,(select name from meta_script_tab where name = 'mem_doc_'||nvl(base_mt.intl_id, m.intl_id)) mem_doc_ddic
,(select name from meta_script_tab where name = 'doc_'||nvl(base_mt.intl_id, m.intl_id)) doc_ddic
,(select name from meta_script_tab where name = 'obj_'||nvl(base_mt.intl_id, m.intl_id)) obj_ddic
,(select case when count(distinct src_text_type_id) = 2 then 'true' when s.id is not null then 'false' end
from src_hist
where 1=1
and src_id = s.id
and seq_nr = 0) has_override
,case when m.id not in (225 /*WZRD*/, 134 /*ACCSWIT*/, 190 /*CNTPRTY*/, 250 /*TERRAVIS*/) then '+' end extr_wfc
--,m.*
from meta_typ_v m
,meta_typ_v base_mt
,code_form_v cf
,src s
where 1=1
and nvl(m.base_meta_typ_id, 0) not in (225 /*WZRD*/)
and cf.intl_id (+)= m.intl_id
and s.src_type_id (+)= 202
and s.ref_id (+)= cf.intf_def_id
and base_mt.id (+)= m.base_meta_typ_id
--and cf.id != m.id
--and m.intl_id like 'bp%'
order by m.id, /*m.activ nulls last, */ o, m.name
) loop
xml#write(' <metatyp id="'||c.meta_typ_id||'" intlId="'||xml#encode(c.intl_id)||'" name="'||xml#encode(c.name)||'" active="'||xml#encode(c.active)||'" ');
if c.base_meta_typ is not null then xml#write('baseMetatyp="'||c.base_meta_typ||'" '); end if;
if c.form is not null then xml#write('form="'||c.form||'" '); end if;
if c.mem_doc_ddic is not null then xml#write('memDDIC="'||c.mem_doc_ddic||'" '); end if;
if c.doc_ddic is not null then xml#write('docDDIC="'||c.doc_ddic||'" '); end if;
if c.obj_ddic is not null then xml#write('objDDIC="'||c.obj_ddic||'" '); end if;
xml#write_line('source="'||xml#encode(c.src_name)||'" sourceOwner="'||xml#encode(c.src_owner)||'" sourceOverriden="'||c.has_override||'" />');
-- Extract corresponding source
extr_raw_src(c.src_id);
-- Generate VIZ file:
begin
if c.extr_wfc = '+' then
if c.meta_typ_id = def_meta_typ.stex then
-- Fix broken STEX WF
update wfc_action
set stmt = replace(stmt, '(commit))', '(commit)')
where meta_typ_id = 1
and id = 6521;
elsif c.meta_typ_id = def_meta_typ.realty then
-- Fix broken REALTY WF
update wfc_action
set stmt = ''
where meta_typ_id = 36
and id = 9;
elsif c.meta_typ_id = def_meta_typ.wup then
-- Fix missing WFS 30
update wfc_action
set stmt = replace(stmt, '(trans(30))', '(trans('||old_wfc_status_id||'))')
where meta_typ_id = 221
and stmt like '%trans(30)%';
update wfc_action
set old_wfc_status_id = new_wfc_status_id
where meta_typ_id = 221
and old_wfc_status_id = 30;
end if;
if c.base_meta_typ_id is null then
l_wfc_file := vis_wfc#.write_viz_file(i_meta_typ_id=>c.meta_typ_id);
else
l_wfc_file := vis_wfc#.write_viz_file(i_meta_typ_id=>c.base_meta_typ_id, i_form_id=>c.form_id);
end if;
file#.file#set_trl(l_wfc_file, b_dest_path||'/workflow/');
file#.file#store_file_idx(l_wfc_file);
-- Rename the generated file:
begin
utl_file.frename('AAA_DB_IO', file#.file#filename(l_wfc_file), 'AAA_DB_IO', c.viz_file, true);
update file_idx
set filename = c.viz_file
where id = file#.file#id(l_wfc_file);
commit;
exception
when others then
install#.log#write('Failed to rename file '||file#.file#filename(l_wfc_file)||' to '||c.viz_file);
end;
end if;
exception
when others then
l_wfc_file := null;
end;
end loop;
xml#write_line('</metatyplist>');
xml#finalize;
move_files_to_dir;
commit;
exception
when others then
raise_fa_err('gen_metatypes');
end gen_metatypes;
----------------------------------------------------------------------------
procedure extr_taskdef(
i_taskdef_obj_id number
)
is
l_saved_state varchar2(32760);
l_curr_main_sep varchar2(200);
l_curr_sub_sep varchar2(200);
l_curr_lbl_sep varchar2(200);
begin
l_saved_state := current_state;
for c in (
select /*+leading(otd)*/
otd.obj_id
,meta_out_id
,ot_name.text name
,nvl(ot_hira.text, 'undefined') hira
,nvl(otd.type, 'undefined') type
,case when otd.descn != ot_name.text then otd.descn
end descn
,ctdt.name task_def_type
,otd.batch
,otd.show
,otd.single_login
,otd.dtm_plh_tab
,(select intl_id from code_script_ctx where id = otd.script_ctx_id) script_ctx
,otd.virtual
,get_obj(otd.impl_task_def_id) impl_task_def
,otd.task_has_no_par
--,(select intl_id from code_form_v where id = otd.form_id) form
,s.id src_id, s.name src_name, (select user_id from code_src_owner where id = s.src_owner_id) src_owner
,(select case when count(distinct src_text_type_id) = 2 then 'true' when s.id is not null then 'false' end
from src_hist
where 1=1
and src_id = s.id
and seq_nr = 0) has_override
from obj_task_def otd
,code_task_def_type ctdt
,src s
,out_text_cmpl ot_name
,out_text_cmpl ot_hira
where 1=1
and ctdt.id (+)= otd.task_def_type_id
and s.id = otd.src_id
and ot_name.key (+)= 'TASK_DEF.'||otd.obj_id
and ot_name.start_lang_id (+)= -1
and ot_hira.key (+)= 'TASK_DEF.HIRA.'||otd.hira
and ot_hira.start_lang_id (+)= -1
and obj_id = i_taskdef_obj_id
order by decode(otd.type, 'report', 'AAA', 'program', 'BBB', otd.type), hira, name
) loop
xml#new(lower(c.src_name)||'.xml', 'taskdef');
xml#write('<taskdef metaOutId="'||c.meta_out_id||'" name="'||xml#encode(c.name)||'" type="'||xml#encode(c.type)||'" taskDefType="'||xml#encode(c.task_def_type)||'" taskDefHierarchy="'||xml#encode(c.hira)||'" ');
if c.batch is not null then xml#write('batch="true" '); end if;
if c.show is not null then xml#write('show="true" '); end if;
if c.single_login is not null then xml#write('singleLogin="true" '); end if;
if c.virtual is not null then xml#write('isVirtual="true" '); end if;
xml#write_line('source="'||xml#encode(c.src_name)||'" sourceOwner="'||xml#encode(c.src_owner)||'" sourceOverriden="'||c.has_override||'">');
if c.descn is not null then
xml#write_line('<descn>'||xml#encode(c.descn)||'</descn>');
end if;
l_curr_main_sep := '#';
l_curr_sub_sep := '#';
l_curr_lbl_sep := '#';
if c.task_has_no_par is null then
for c2 in (
select p.id
,last_value(case when p.style_id||p.type = '1separator' then ot_name.text end ignore nulls) over (order by order_by_form) main_sep
,last_value(case when p.style_id||p.type = '2separator' then ot_name.text end ignore nulls) over (order by order_by_form) sub_sep
,last_value(case when p.style_id||p.type = '3separator' then ot_name.text end ignore nulls) over (order by order_by_form) lbl_sep
,nvl(ot_name.text, p.prompt) humanName
,p.par_name name
,p.type, p.ctype domain, p.cond constraint
,p.is_list_of
,p.is_set_of
,p.mand
,p.abstr
,p.descn
from obj_task_def_par p
,out_text_cmpl ot_name
where 1=1
and task_def_id = c.obj_id
--
and ot_name.key (+)= p.out_text_label_key
and ot_name.start_lang_id (+)= -1
order by order_by_form
) loop
if nvl(c2.main_sep, '#') != l_curr_main_sep then
if l_curr_main_sep != '#' then
xml#write_line('</paramGroup>');
end if;
l_curr_main_sep := c2.main_sep;
if l_curr_main_sep != '#' then
xml#write_line('<paramGroup name="'||xml#encode(l_curr_main_sep)||'">');
end if;
end if;
if nvl(c2.sub_sep, '#') != l_curr_sub_sep then
if l_curr_sub_sep != '#' then
xml#write_line('</paramGroup>');
end if;
l_curr_sub_sep := c2.sub_sep;
if l_curr_sub_sep != '#' then
xml#write_line('<paramGroup name="'||xml#encode(l_curr_sub_sep)||'">');
end if;
end if;
if nvl(c2.lbl_sep, '#') != l_curr_lbl_sep then
if l_curr_lbl_sep != '#' then
xml#write_line('</paramGroup>');
end if;
l_curr_lbl_sep := c2.lbl_sep;
if l_curr_lbl_sep != '#' then
xml#write_line('<paramGroup name="'||xml#encode(l_curr_lbl_sep)||'">');
end if;
end if;
if c2.type != 'separator' then
xml#write('<param id="'||xml#encode(c2.id)||'" name="'||xml#encode(c2.name)||'" humanName="'||xml#encode(c2.humanName)||'" type="'||xml#encode(c2.type)||'" ');
if c2.domain is not null then xml#write('domain="'||xml#encode(c2.domain)||'" '); end if;
if c2.mand is not null then xml#write('mandatory="true" '); end if;
if c2.abstr is not null then xml#write('abstract="true" '); end if;
if c2.descn is not null then
xml#write_line('><descn>'||xml#encode(c2.descn)||'</descn></param>');
else
xml#write_line('/>');
end if;
end if;
end loop;
end if;
if l_curr_lbl_sep != '#' then
xml#write_line('</paramGroup>');
end if;
if l_curr_sub_sep != '#' then
xml#write_line('</paramGroup>');
end if;
if l_curr_main_sep != '#' then
xml#write_line('</paramGroup>');
end if;
end loop;
xml#write_line('</taskdef>');
xml#finalize;
commit;
restore_state(l_saved_state);
exception
when others then
restore_state(l_saved_state);
raise_fa_err('extr_taskdef');
end extr_taskdef;
----------------------------------------------------------------------------
procedure gen_taskdeflist
is
l_curr_main_sep varchar2(200);
l_curr_sub_sep varchar2(200);
l_curr_lbl_sep varchar2(200);
begin
xml#new('02_taskdefs.xml');
xml#write_line('<taskdeflist>');
for c in (
select /*+leading(otd)*/
otd.obj_id
,meta_out_id
,ot_name.text name
,nvl(ot_hira.text, 'undefined') hira
,nvl(otd.type, 'undefined') type
,case when otd.descn != ot_name.text then otd.descn
end descn
,ctdt.name task_def_type
,otd.batch
,otd.show
,otd.single_login
,otd.dtm_plh_tab
,(select intl_id from code_script_ctx where id = otd.script_ctx_id) script_ctx
,otd.virtual
,get_obj(otd.impl_task_def_id) impl_task_def
,otd.task_has_no_par
--,(select intl_id from code_form_v where id = otd.form_id) form
,s.id src_id, s.name src_name, (select user_id from code_src_owner where id = s.src_owner_id) src_owner
,(select case when count(distinct src_text_type_id) = 2 then 'true' when s.id is not null then 'false' end
from src_hist
where 1=1
and src_id = s.id
and seq_nr = 0) has_override
from obj_task_def otd
,code_task_def_type ctdt
,src s
,out_text_cmpl ot_name
,out_text_cmpl ot_hira
where 1=1
and ctdt.id (+)= otd.task_def_type_id
and s.id = otd.src_id
and ot_name.key (+)= 'TASK_DEF.'||otd.obj_id
and ot_name.start_lang_id (+)= -1
and ot_hira.key (+)= 'TASK_DEF.HIRA.'||otd.hira
and ot_hira.start_lang_id (+)= -1
--and meta_out_id = 2092
--and obj_id in (101368,18925629,19821435,29344838,87107,2789847)
--and otd.cover is not null
order by decode(otd.type, 'report', 'AAA', 'program', 'BBB', otd.type), hira, name
) loop
xml#write(' <taskdefref metaOutId="'||c.meta_out_id||'" name="'||xml#encode(c.name)||'" type="'||xml#encode(c.type)||'" taskDefHierarchy="'||xml#encode(c.hira)||'" ');
if c.batch is not null then xml#write('batch="true" '); end if;
if c.show is not null then xml#write('show="true" '); end if;
if c.single_login is not null then xml#write('singleLogin="true" '); end if;
xml#write_line('source="'||xml#encode(c.src_name)||'" sourceOwner="'||xml#encode(c.src_owner)||'" sourceOverriden="'||c.has_override||'" />');
-- Extract corresponding taskdef details
extr_taskdef(c.obj_id);
-- Extract corresponding source
extr_raw_src(c.src_id);
end loop;
xml#write_line('</taskdeflist>');
xml#finalize;
move_files_to_dir;
commit;
exception
when others then
raise_fa_err('gen_taskdeflist');
end gen_taskdeflist;
----------------------------------------------------------------------------
-- Prototype for new XML file
/*
procedure gen_prcqs
is
begin
xml#new('02_prcqs.xml');
xml#write_line('<prcqlist>');
for c in (
select '' xml
from dual
where 1=1
) loop
xml#write_line(c.xml);
end loop;
xml#write_line('</prcqlist>');
xml#finalize;
commit;
exception
when others then
raise_fa_err('gen_prcqs');
end gen_prcqs;
*/
----------------------------------------------------------------------------
procedure gen_all
is
begin
gen_base_files;
gen_summary;
gen_releases;
gen_bgps;
gen_prcqs;
gen_metatypes;
gen_taskdeflist;
gen_ddiclist;
gen_all_ddics;
move_files_to_dir;
gen_zip;
exception
when others then raise_fa_err('gen_all');
end gen_all;
----------------------------------------------------------------------------
-- RELEVANT PROCEDURES TO RUN A SHELL COMMAND
----------------------------------------------------------------------------
-- caller specifications -----------------------------------------------------
-- os_execute ----------------------------------------------------------------
function os_execute(o_stdout OUT VARCHAR2,
o_stderr OUT VARCHAR2,
i_cmd IN VARCHAR2,
i_envp IN T_CHAR32000_TAB,
i_dir IN VARCHAR2,
i_mode IN NUMBER)
RETURN NUMBER AS
LANGUAGE java name 'com.avaloq.server.os.OsExecute.execute(java.lang.String[]
,java.lang.String[]
,java.lang.String
,oracle.sql.ARRAY
,java.lang.String
,int
) return int';
-- ---------------------------------------------------------------------------
-- os_cmd --------------------------------------------------------------------
procedure os_cmd(i_cmd in varchar2, o_stdout out varchar2, o_stderr out varchar2, o_return out number, i_dir varchar2 := null)
is
c_procedure varchar2(30) := 'os_cmd';
c_envp_ospath constant varchar2(500) := '<i>PATH</i>=<i>"/usr/xpg4/bin:/usr/ccs/bin:/usr/bin:/usr/sbin:/bin:/sbin:/usr/ucb:/usr/local/bin"</i>';
--+-------------------------------------------------------------------------
c_throw_if_not_rc0 constant pls_integer := 1;
c_include_stdouterr constant pls_integer := 2;
c_include_cmd constant pls_integer := 4;
c_shell_style_parse constant pls_integer := 8;
c_full_trace_if_not_rc0 constant pls_integer := 16;
c_full_trace_if_error constant pls_integer := 32;
c_mode constant integer := c_throw_if_not_rc0 + c_include_stdouterr;
--+-------------------------------------------------------------------------
l_envp k.t_char32000_tab;
l_return number := null;
begin
l_envp := k.t_char32000_tab(c_envp_ospath);
l_return := os_execute(o_stdout => o_stdout,
o_stderr => o_stderr,
i_cmd => i_cmd,
i_envp => l_envp,
i_dir => i_dir,
i_mode => c_mode);
exception
when others then
o_stdout := trim(regexp_replace(o_stdout,'^ *$'));
o_stderr := trim(regexp_replace(o_stderr,'^ *$'));
raise;
end os_cmd;
procedure run_os_cmd(i_cmd in varchar2, i_dir in varchar2 := null)
is
c_procedure varchar2(30) := 'run_os_cmd';
l_stdout varchar2(32767);
l_stderr varchar2(32767);
l_return number;
begin
os_cmd(i_cmd => i_cmd, o_stdout => l_stdout, o_stderr => l_stderr, o_return => l_return, i_dir => i_dir);
dbms_output.put_line(c_procedure||' : return ['||l_return||']');
dbms_output.put_line(c_procedure||' : stdout ['||l_stdout||']');
dbms_output.put_line(c_procedure||' : stderr ['||l_stderr||']');
exception
when others then
dbms_output.put_line(c_procedure||' : return [null]');
dbms_output.put_line(c_procedure||' : stdout ['||trim(regexp_replace(l_stdout,'^ *$'))||']');
dbms_output.put_line(c_procedure||' : stderr ['||trim(regexp_replace(l_stderr,'^ *$'))||']');
dbms_output.put_line(c_procedure||' : error - others - '||sqlerrm);
end run_os_cmd;
procedure gen_zip
is
begin
require(b_dest_path is not null, 'Valid dest path');
for c in (
select directory_path||'/'||b_dest_path dir, b_dest_path||'.zip' filename
from all_directories
where directory_name = 'AAA_DB_IO'
) loop
dbms_output.put_line('Generating Zip '||c.filename||' in '||c.dir);
run_os_cmd('zip -rq ../'||c.filename||' .', c.dir);
end loop;
end gen_zip;
-- Helper function called from TASK_SCRIPT_DDIC.RBSC$XML (REP SCRIPT) used to get the content of the DESCN column
function script#fld_descn(
i_tab_name varchar2
,i_fld_name varchar2
) return varchar2
is
l_descn varchar2(32000);
begin
execute immediate 'select descn from '||i_tab_name||' where intl_id = :fld_name'
into l_descn
using i_fld_name;
return l_descn;
exception
when others then
return null;
end script#fld_descn;
-- Helper function called from TASK_SCRIPT_DDIC.RBSC$XML (REP SCRIPT) used to get the content of the NAME column
function script#fld_name(
i_tab_name varchar2
,i_fld_name varchar2
) return varchar2
is
l_descn varchar2(32000);
begin
execute immediate 'select name from '||i_tab_name||' where intl_id = :fld_name'
into l_descn
using i_fld_name;
return l_descn;
exception
when others then
return null;
end script#fld_name;
end;
/
