Easy way to search text in ALL triggers possible ?

[saustin]

Hi,

Is there any way to search for text located in all triggers in a schema ? For example, if you are looking for all the triggers who reference 'REDSOX'. Just looking for some global search improvements. Many thanks for any suggestons. Steve.

 

[SantaMufasa]

Steve,

Below is a proof-of-concept, followed by the function code that lists trigger components that contain a string of your choosing:

Section 1 -- Proof-of-concept:

set linesize 200
col a heading "Trigger name" format a30
col b heading "Trigger components containing search string" format a50
select trigger_name a, search_triggers(trigger_name,'e') b from user_triggers
 where search_triggers(trigger_name,'e') is not null;

Trigger name                   Trigger components containing search string
------------------------------ -------------------------------------------
EMP_COMMIT                     "e" in: TRIGGER_NAME, TRIGGER_BODY
ORGUNIT_TRG                    "e" in: TRIGGER_BODY
PREVENT_DELETES                "e" in: TRIGGER_NAME, TRIGGER_BODY

3 rows selected.

select trigger_name a, search_triggers(trigger_name,'x') b from user_triggers
	where search_triggers(trigger_name,'x') is not null;

Trigger name                   Trigger components containing search string
------------------------------ -------------------------------------------
ORGUNIT_TRG                    "x" in: TRIGGER_BODY

1 row selected.

select trigger_name a, search_triggers(trigger_name,'REDSOX') b from user_triggers
 where search_triggers(trigger_name,'REDSOX') is not null;

no rows selected.

Section 2 -- "search_triggers" function code:

create or replace function search_triggers
	(trig_name varchar2, search_string varchar2)
	return varchar2
is
	body_hold	varchar2(32765);
	message		varchar2(4000)	:= '"'||search_string||'" in: ';
	found_string	boolean;
	str		varchar2(2000);
	sepchr		varchar2(10);
begin
	for r in (select * from user_triggers
			where trigger_name = trig_name) loop
		body_hold := r.trigger_body;
		str	:= upper(search_string);
		found_string	:= false;
		if r.trigger_name like '%'||str||'%' then
			message	:= message||sepchr||'TRIGGER_NAME';
			found_string	:= true;
			sepchr		:= ', ';
		end if;
		if body_hold like '%'||str||'%' then
			message	:= message||sepchr||'TRIGGER_BODY';
			found_string	:= true;
			sepchr		:= ', ';
		end if;
	end loop;
	if found_string then
		return message;
	else
		return null;
	end if;
end;
/

Function created.

Let us know if this does what you want.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 01:16 (19Nov04) UTC (aka "GMT" and "Zulu"),
@ 18:16 (18Nov04) Mountain Time

 

[markrem]

I don't know which version of Oracle, but probably 8i due to this being the 8i forum. I haven't had 8i in a while, but let's just say that in the more recent versions of ORacle the trigger code is not all visible in the *_TRIGGERS views such as ALL_TRIGGERS. It truncates the source code after 4000 bytes. Take a look at USER_SOURCE or ALL_SOURCE. Oracle breaks the PL/SQL in to a series of 4K pages and only the first page is available to view ALL_TRIGGERS. Another trap in 10g is the use of the recycle bin. You need to exclude lines of code related to objects you altered or deleted, because they now reside in your recycle bin.

For example, build your cursor using something like this:

select name, line, type, text
from user_source
where type = 'TRIGGER'
and name not like 'BIN$%'
order by 1, 2, 3;

Now, loop through looking for your search string.

-Mark

9i OCP