बजट सक्रियण के लिए स्क्रिप्टिंग (भाग 1)

create or replace package body ae_scripting as g_if_setting constant varchar2(100) default 'if_condition'; g_foreach_setting constant varchar2(100) default 'foreach_var'; g_target_setting constant varchar2(100) default 'target_platform'; g_if_statement constant varchar2(100) default 'if'; g_foreach_statement constant varchar2(100) default 'foreach'; procedure extract(p_id in number) as cursor c_cmd is select d.id, d.nm, d.lv, d.type_id, p.name platform, t0.value target, f.direction_id direction from ( select level lv, sys_connect_by_path(to_char(c.nm, '00000'), '/') pt, sys_connect_by_path(c.id, '.') nm, c.id id, c.type_id from ( select a.id id, a.type_id type_id, b.parent_id parent_id, b.order_num nm from ae_script_part a, ae_subscript b where b.child_id(+) = a.id ) c start with c.id = p_id connect by prior c.id = c.parent_id order by pt ) d left join ae_script e on (e.id = d.id) left join ae_command f on (f.id = d.id) left join ae_platform p on (p.id = e.platform_id) left join ae_setting t0 on (t0.object_id = d.id and t0.setting_type_id = 1000001); r_cmd c_cmd%rowtype; cursor c_par(p_cmd_id number) is select t.name name, s.value value from ae_script_part a inner join ae_setting s on (s.object_id = a.id) inner join ae_setting_type t on (t.id = s.setting_type_id) where a.id = p_cmd_id union all select t.name, s.value from ae_script_part a inner join ae_command c on (c.id = a.id) inner join ae_setting s on (s.object_id = c.template_id) inner join ae_setting_type t on (t.id = s.setting_type_id) where a.id = p_cmd_id; r_par c_par%rowtype; l_str varchar2(1000) default null; l_lob CLOB; l_lvl number default 0; l_plv number default 0; l_cnt number default null; l_stt varchar2(50) default null; begin delete from ae_script_src where id = p_id; insert into ae_script_src(id, text) values (p_id, empty_clob()); select text into l_lob from ae_script_src where id = p_id; dbms_lob.open(l_lob, dbms_lob.lob_readwrite); open c_cmd; loop fetch c_cmd into r_cmd; exit when c_cmd%notfound; while r_cmd.lv <= l_plv loop l_str := ' ' || lpad('}', 2 * l_lvl) || chr(13) || chr(10); dbms_lob.writeappend(l_lob, length(l_str), l_str); l_plv := l_plv - 1; l_lvl := l_lvl - 1; end loop; l_str := '[' || trim(to_char(r_cmd.id, '000000')) || '] '; if not r_cmd.direction is null then if r_cmd.direction = '1' then l_str := l_str || '<'; else l_str := l_str || '>'; end if; else l_str := l_str || ' '; end if; l_str := l_str || lpad(' ', 2 * r_cmd.lv); if not r_cmd.platform is null then l_str := l_str || 'platform:' || r_cmd.platform || '; '; end if; if not r_cmd.target is null then l_str := l_str || 'target:' || r_cmd.target || '; '; end if; open c_par(r_cmd.id); l_stt := null; loop fetch c_par into r_par; exit when c_par%notfound; if l_stt is null and r_par.name = g_if_setting then l_str := l_str || g_if_statement || ' (' || r_par.value || ') { '; l_stt := r_par.name; end if; if l_stt is null and r_par.name = g_foreach_setting then l_str := l_str || g_foreach_statement || ' (' || r_par.value || ') { '; l_stt := r_par.name; end if; end loop; close c_par; open c_par(r_cmd.id); loop fetch c_par into r_par; exit when c_par%notfound; if l_stt is null or l_stt <> r_par.name then if r_par.name <> g_target_setting then l_str := l_str || r_par.name || ':' || r_par.value || '; '; end if; end if; end loop; close c_par; select count(*) into l_cnt from ae_subscript where parent_id = r_cmd.id; if r_cmd.type_id = 1 and l_cnt > 0 then if l_stt is null then l_str := l_str || '{'; end if; l_lvl := l_lvl + 1; l_plv := r_cmd.lv; else if not l_stt is null then l_str := l_str || ' }'; end if; l_plv := r_cmd.lv - 1; end if; l_str := l_str || chr(13) || chr(10); dbms_lob.writeappend(l_lob, length(l_str), l_str); end loop; close c_cmd; while l_lvl > 0 loop l_str := ' ' || lpad('}', 2 * l_lvl) || chr(13) || chr(10); dbms_lob.writeappend(l_lob, length(l_str), l_str); l_lvl := l_lvl - 1; end loop; dbms_lob.close(l_lob); commit; exception when others then if c_cmd%isopen then close c_cmd; end if; if c_par%isopen then close c_par; end if; if dbms_lob.isopen(l_lob) = 1 then dbms_lob.close(l_lob); end if; rollback; raise; end; end ae_scripting; / 

 [001420] target:ats.type; foreach (params) { [003101] platform:S-12; if (dou_off.dou = 'REDIRECT_NOANSWER') { [031010] < text:MODIFY-SUBSCR:DN=K'%s,CFWD=DEACT&CFWDNOR.; var_list:phone; [001041] > regexp:(.+); var_list:error_text; is_error:1; [001008] platform:M-200; var_list:is_redirect_param = 1; } [003111] platform:S-12; if (dou_off.dou = 'REDIRECT_BUSY') { [031110] < text:MODIFY-SUBSCR:DN=K'%s, CFWD=DEACT&CFWDBSUB.; var_list:phone; [001041] > regexp:(.+); var_list:error_text; is_error:1; [001008] platform:M-200; var_list:is_redirect_param = 1; } [003121] platform:S-12; if (dou_off.dou = 'REDIRECT_AUTOINF') { [031210] < text:MODIFY-SUBSCR:DN=K'%s, CFWD=DEACT&CFWDFIXA.; var_list:phone; [001041] > regexp:(.+); var_list:error_text; is_error:1; [001008] platform:M-200; var_list:is_redirect_param = 1; } [003131] platform:S-12; if (dou_off.dou = 'REDIRECT') { [031310] < text:MODIFY-SUBSCR:DN=K'%s, CFWD=DEACT&CFWDUVAR.; var_list:phone; [001041] > regexp:(.+); var_list:error_text; is_error:1; [001008] platform:M-200; var_list:is_redirect_param = 1; } [003071] platform:S-12; if (dou_off.dou = 'SET_ALARM_CLOCK') { [030710] < text:MODIFY-SUBSCR:DN=K'%s,ALMCALL=DEACT.; var_list:phone; [001041] > regexp:(.+); var_list:error_text; is_error:1; [001009] var_list:is_alarm_param = 1; } }