Siebel SQLs/Error Messages >  Find text in Siebel Tools code

Find text in Siebel Tools code

Run this SQL, when there is a popup, enter your code to search. This will search the Tools Database Tables

 

declare
p_text_to_search varchar2(255) := '%' || '&p_text_to_search' || '%';
l_txt varchar2(255) := '&p_text_to_search';
p_object_name varchar2(255) := nvl('&p_object_name', '%');
p_print_context boolean := case nvl('&p_print_context', 'N')
when 'N' then
false
when 'Y' then
true
else
false
end;

p_repository siebel.s_repository.name%type := 'Siebel Repository';

l_output_header varchar2(600);
l_lng varchar2(32000);

function getlong(p_tname in varchar2,
p_cname in varchar2,
p_rowid in rowid) return varchar2 as
l_cursor integer default dbms_sql.open_cursor;
l_long_val varchar2(32000);
l_long_len number;
begin
dbms_sql.parse(l_cursor,
'select ' || p_cname || ' from siebel.' || p_tname ||
' where rowid = :x',
dbms_sql.native);
dbms_sql.bind_variable(l_cursor, ':x', p_rowid);
dbms_sql.define_column_long(l_cursor, 1);
l_long_len := dbms_sql.execute(l_cursor);
if (dbms_sql.fetch_rows(l_cursor) > 0) then
dbms_sql.column_value_long(c => l_cursor,
position => 1,
length => 32000,
offset => 0,
value => l_long_val,
value_length => l_long_len);
end if;
dbms_sql.close_cursor(l_cursor);
return l_long_val;
end getlong;

procedure printlines(p_long in varchar2) as
l_occurrence number;
l_str varchar2(32000);
begin
l_occurrence := 1;
while instr(p_long, l_txt, 1, l_occurrence) <> 0 loop
l_str := replace(regexp_substr(p_long,
'^.*' || l_txt || '.*$',
1,
l_occurrence,
'm'),
chr(9),
' ');
if l_str is not null then
dbms_output.put_line(' | ' || l_str);
dbms_output.put_line(' ' || rpad(' ', instr(l_str, l_txt), ' ') ||
rpad('^', length(l_txt), '^'));
end if;
l_occurrence := l_occurrence + 1;
end loop;
end printlines;

begin
l_output_header := 'Search "' || p_text_to_search || '" in "' ||
p_object_name || '"';
dbms_output.put_line(l_output_header);
dbms_output.put_line('Context Output: ' || case p_print_context when true then 'ON' when
false then 'OFF' else 'UNDEFINED' end);
dbms_output.put_line(rpad('=', length(l_output_header), '='));

--Application Script
for c in (select ap.name appl, aps.name method, aps.script, aps.rowid
from siebel.s_application ap
inner join siebel.s_appl_script aps
on ap.row_id = aps.application_id
inner join siebel.s_repository rep
on ap.repository_id = rep.row_id
and aps.repository_id = rep.row_id
where rep.name = p_repository
and ap.name like p_object_name
order by 1, 2) loop
l_lng := getlong(p_tname => 's_appl_script',
p_cname => 'script',
p_rowid => c.rowid);
if l_lng like p_text_to_search then
dbms_output.put_line('Application: ' || c.appl || ', Method: ' ||
c.method);
if p_print_context then
printlines(p_long => l_lng);
end if;
end if;
end loop;
--Applet Script
for c in (select ap.name applet, aps.name method, aps.script, aps.rowid
from siebel.s_applet ap
inner join siebel.s_appl_webscrpt aps
on ap.row_id = aps.applet_id
inner join siebel.s_repository rep
on ap.repository_id = rep.row_id
and aps.repository_id = rep.row_id
where rep.name = p_repository
and ap.name like p_object_name
order by 1, 2) loop
l_lng := getlong(p_tname => 's_appl_webscrpt',
p_cname => 'script',
p_rowid => c.rowid);
if l_lng like p_text_to_search then
dbms_output.put_line('Applet: ' || c.applet || ', Method: ' ||
c.method);
if p_print_context then
printlines(p_long => l_lng);
end if;
end if;
end loop;
--Applet User Property
for c in (select ap.name applet, up.name user_prop, up.value
from siebel.s_applet ap
inner join siebel.s_applet_uprop up
on ap.row_id = up.applet_id
inner join siebel.s_repository rep
on ap.repository_id = rep.row_id
and up.repository_id = rep.row_id
where rep.name = p_repository
and ap.name like p_object_name
and up.value like p_text_to_search
order by 1, 2) loop
dbms_output.put_line('Applet: ' || c.applet || ', User Property: ' ||
c.user_prop);
if p_print_context then
printlines(p_long => c.value);
end if;
end loop;
--BC Script
for c in (select bc.name buscomp, bcs.name method, bcs.script, bcs.rowid
from siebel.s_buscomp bc
inner join siebel.s_buscomp_script bcs
on bc.row_id = bcs.buscomp_id
inner join siebel.s_repository rep
on bc.repository_id = rep.row_id
and bcs.repository_id = rep.row_id
where rep.name = p_repository
and bc.name like p_object_name
order by 1, 2) loop
l_lng := getlong(p_tname => 's_buscomp_script',
p_cname => 'script',
p_rowid => c.rowid);
if l_lng like p_text_to_search then
dbms_output.put_line('BC: ' || c.buscomp || ', Method: ' || c.method);
if p_print_context then
printlines(p_long => l_lng);
end if;
end if;
end loop;
--BC Calculated Field
for c in (select bc.name buscomp, fld.name field, fld.calcval
from siebel.s_buscomp bc
inner join siebel.s_field fld
on bc.row_id = fld.buscomp_id
inner join siebel.s_repository rep
on bc.repository_id = rep.row_id
and fld.repository_id = rep.row_id
where rep.name = p_repository
and bc.name like p_object_name
and fld.calcval like p_text_to_search
order by 1, 2) loop
dbms_output.put_line('BC: ' || c.buscomp || ', Calculated Field: ' ||
c.field);
if p_print_context then
printlines(p_long => c.calcval);
end if;
end loop;
--BC User Property
for c in (select bc.name buscomp, up.name user_prop, up.value
from siebel.s_buscomp bc
inner join siebel.s_buscomp_uprop up
on bc.row_id = up.buscomp_id
inner join siebel.s_repository rep
on bc.repository_id = rep.row_id
and up.repository_id = rep.row_id
where rep.name = p_repository
and bc.name like p_object_name
and up.value like p_text_to_search
order by 1, 2) loop
dbms_output.put_line('BC: ' || c.buscomp || ', User Property: ' ||
c.user_prop);
if p_print_context then
printlines(p_long => c.value);
end if;
end loop;
--BS Script
for c in (select sv.name service, sc.name method, sc.script, sc.rowid
from siebel.s_service_scrpt sc
inner join siebel.s_service sv
on sc.service_id = sv.row_id
inner join siebel.s_repository rep
on sv.repository_id = rep.row_id
and sc.repository_id = rep.row_id
where rep.name = p_repository
and sv.name like p_object_name
order by 1, 2) loop
l_lng := getlong(p_tname => 's_service_scrpt',
p_cname => 'script',
p_rowid => c.rowid);
if l_lng like p_text_to_search then
dbms_output.put_line('BS: ' || c.service || ', Method: ' || c.method);
if p_print_context then
printlines(p_long => l_lng);
end if;
end if;
end loop;
--RT BS Script
for c in (select sv.name service, sc.name method, sc.script, sc.rowid
from siebel.s_rt_svc_scrpt sc
inner join siebel.s_rt_svc sv
on sc.rt_svc_id = sv.row_id
where sv.name like p_object_name
order by 1, 2) loop
l_lng := getlong(p_tname => 's_rt_svc_scrpt',
p_cname => 'script',
p_rowid => c.rowid);
if l_lng like p_text_to_search then
dbms_output.put_line('RT BS: ' || c.service || ', Method: ' ||
c.method);
if p_print_context then
printlines(p_long => l_lng);
end if;
end if;
end loop;
--Smartscript Script
for c in (select ss.name sscript, sc.name method, sc.script, sc.rowid
from siebel.s_cs_path_scpt sc
inner join siebel.s_cs_path ss
on sc.path_id = ss.row_id
where ss.name like p_object_name
order by 1, 2) loop
l_lng := getlong(p_tname => 's_cs_path_scpt',
p_cname => 'script',
p_rowid => c.rowid);
if l_lng like p_text_to_search then
dbms_output.put_line('SmartScript: ' || c.sscript || ', Method: ' ||
c.method);
if p_print_context then
printlines(p_long => l_lng);
end if;
end if;
end loop;
--Smartscript Question
for c in (select ss_1.name sscript,
qu_1.name question,
scr.name method,
scr.script,
scr.rowid
from (select distinct ss.row_id ss_id, pg.row_id page_id
from siebel.s_cs_page pg
left outer join siebel.s_cs_edge p_ed
on pg.row_id = p_ed.next_page_id
left outer join siebel.s_cs_path ss
on ss.row_id = p_ed.path_id
or ss.start_page_id = pg.row_id) v1
inner join (select distinct q_pg.row_id page_id,
qu.row_id quest_id
from siebel.s_cs_quest qu
left outer join siebel.s_cs_edge q_ed
on qu.row_id = q_ed.next_quest_id
left outer join siebel.s_cs_page q_pg
on q_pg.start_quest_id = qu.row_id
and q_ed.next_quest_id is null
or q_ed.page_id = q_pg.row_id) v2
on v1.page_id = v2.page_id
inner join siebel.s_cs_path ss_1
on ss_1.row_id = v1.ss_id
inner join siebel.s_cs_quest qu_1
on qu_1.row_id = v2.quest_id
inner join siebel.s_cs_quest_scpt scr
on qu_1.row_id = scr.quest_id
where ss_1.name like p_object_name
or qu_1.name like p_object_name
order by 1, 2, 3) loop
l_lng := getlong(p_tname => 's_cs_quest_scpt',
p_cname => 'script',
p_rowid => c.rowid);
if l_lng like p_text_to_search then
dbms_output.put_line('SmartScript: ' || c.sscript || ', Question: ' ||
c.question || ', Method: ' || c.method);
if p_print_context then
printlines(p_long => l_lng);
end if;
end if;
end loop;
--RTE Action
for c in (select scas.name action_set, sca.name action
from siebel.s_ct_action sca
inner join siebel.s_ct_action_set scas
on sca.ct_actn_set_id = scas.row_id
where (sca.set_attr like p_text_to_search or
sca.svc_name like p_text_to_search or
sca.svc_method_name like p_text_to_search or
sca.svc_context like p_text_to_search or
sca.method_name like p_text_to_search or
sca.method_context like p_text_to_search)
and (scas.name like p_object_name or
sca.name like p_object_name)
order by 1, 2) loop
dbms_output.put_line('RTE Action Set: ' || c.action_set ||
', Action: ' || c.action);
end loop;
--RTE Event
for c in (select sce.obj_name,
sce.evt_name,
nvl2(sce.evt_sub_name,
', Sub-Event: ' || sce.evt_sub_name,
'') evt_sub_name,
sce.evt_seq_num,
sce.actn_cond_expr
from siebel.s_ct_event sce
where sce.actn_cond_expr like p_text_to_search
and sce.obj_name like p_object_name
order by 1, 2, 3) loop
dbms_output.put_line('RTE Event - Object: ' || c.obj_name || ' (' ||
c.evt_seq_num || '), Event: ' || c.evt_name ||
c.evt_sub_name);
if p_print_context then
printlines(p_long => c.actn_cond_expr);
end if;
end loop;
--DVM Message and Expressions
for c in (select svrs.name ruleset_name, svr.name rule_name
from siebel.s_valdn_rl_set svrs
left outer join siebel.s_valdn_rule svr
on svr.rule_set_id = svrs.row_id
left outer join siebel.s_iss_valdn_msg sivn
on sivn.row_id = svr.valdn_msg_id
left outer join siebel.s_iss_vmsg_lang sivl
on sivl.par_row_id = sivn.row_id
where svrs.status_cd = 'Active'
and (svrs.cond_expr like p_text_to_search or
svr.rule_expr like p_text_to_search or
svr.err_msg_txt like p_text_to_search or
sivn.msg_text like p_text_to_search or
sivl.msg_text like p_text_to_search)
and (svrs.name like p_object_name or
svr.name like p_object_name)
order by 1, 2) loop
dbms_output.put_line('DVM Ruleset: ' || c.ruleset_name || ', Rule: ' ||
c.rule_name);
end loop;
--WF Process Step and Step Branch Criteria
for c in (select swp.proc_name wf_name,
sws.name step_name,
swsb.name branch_name
from siebel.s_wfr_proc swp
inner join siebel.S_WFR_STP sws
on sws.process_id = swp.row_id
inner join siebel.s_repository rep
on swp.repository_id = rep.row_id
and sws.repository_id = rep.row_id
left outer join siebel.s_wfr_stp_arg swsa
on swsa.step_id = sws.row_id
and swsa.repository_id = rep.row_id
left outer join siebel.s_wfr_stp_brnch swsb
on swsb.step_id = sws.row_id
and swsb.repository_id = rep.row_id
where rep.name = p_repository
and swp.status_cd = 'COMPLETED'
and (sws.name like p_text_to_search or
sws.action_buscomp like p_text_to_search or
sws.method_name like p_text_to_search or
sws.service_name like p_text_to_search or
sws.subprocess_name like p_text_to_search or
sws.comments like p_text_to_search or
swsa.name like p_text_to_search or
swsa.buscomp_name like p_text_to_search or
swsa.buscomp_fld_name like p_text_to_search or
swsa.comments like p_text_to_search or
swsa.buscomp_fld_name like p_text_to_search or
swsa.proc_prop_name like p_text_to_search or
swsa.val like p_text_to_search or
swsb.expr like p_text_to_search)
and (swp.proc_name like p_object_name or
sws.name like p_object_name)) loop
dbms_output.put_line('WF Process: ' || c.wf_name ||
(case when c.step_name is null then '' else
', Step: ' || c.step_name end) ||
(case when c.branch_name is null then '' else
', Branch: ' || c.branch_name end));
end loop;
--EAI Data Map
for c in (select mp.name mp_name, null cmp_name, null fld_name
from siebel.S_INT_OBJMAP mp
where mp.comments like p_text_to_search
and mp.name like p_object_name
union all
select mp.name, cmp.name, null
from siebel.S_INT_OBJMAP mp
inner join siebel.S_INT_COMPMAP cmp
on mp.row_id = cmp.int_obj_map_id
where (cmp.comments like p_text_to_search or
cmp.dst_post_cond like p_text_to_search or
cmp.src_pre_cond like p_text_to_search or
cmp.src_srchspec like p_text_to_search)
and mp.name like p_object_name
union all
select mp.name, cmp.name, fld.dst_int_fld_name
from siebel.S_INT_OBJMAP mp
inner join siebel.S_INT_COMPMAP cmp
on mp.row_id = cmp.int_obj_map_id
inner join siebel.S_INT_FLDMAP fld
on cmp.row_id = fld.int_comp_map_id
where (fld.comments like p_text_to_search or
fld.src_expr like p_text_to_search)
and mp.name like p_object_name) loop
dbms_output.put_line('EAI Data Map: ' || c.mp_name ||
(case when c.cmp_name is null then '' else
', Data Map Component: ' || c.cmp_name end) ||
(case when c.fld_name is null then '' else
', Data Map Field: ' || c.fld_name end));
end loop;
end;