You Asked

Hi Tom,
I want to search a trigger body for some particular text and would like to know the names of all such triggers...

select * from user_triggers where trigger_body like '%CONSULTATION%'

My Oracle Version is 10G Rel 1.

Now my problem is since trigger_body is a long type column , I am unable to use it in the where clause.

Several sites has recommended that we use a cursor to resolve this.

Can you tell me a easy way to do this ? If cursor is reqd. some example ?

One more question why dont we have trigger_body as clob type column ?

Regards,
Piyush

and Tom said...

ops$tkyte%ORA10GR2> create or replace
  2  function trigger_text_like
  3  ( p_schema in varchar2,
  4    p_trigger_name in varchar2,
  5    p_search in varchar2
  6  )
  7  return number
  8  as
  9          l_text long;
 10  begin
 11          select trigger_body
 12            into l_text
 13            from all_triggers
 14           where owner = p_schema
 15             and trigger_name = p_trigger_name;
 16
 17          if ( l_text like p_search )
 18          then
 19                  return 1;
 20          else
 21                  return 0;
 22          end if;
 23  exception
 24          when no_data_found then return null;
 25  end;
 26  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select owner, trigger_name
  2    from all_triggers
  3   where trigger_text_like( owner, trigger_name, '%X%' ) = 1
  4  /

OWNER                          TRIGGER_NAME
------------------------------ ------------------------------
XDB                            XDB$ACL$xd
MDSYS                          OGIS_CRS_INSERT_TRIGGER
MDSYS                          SDO_CRS_INSERT_TRIGGER
MDSYS                          SDO_NETWORK_TRIG_INS
MDSYS                          SDO_GEOM_TRIG_INS1
MDSYS                          SDO_GEOM_TRIG_DEL1
MDSYS                          SDO_GEOM_TRIG_UPD1
MDSYS                          SDO_LRS_TRIG_INS
MDSYS                          SDO_LRS_TRIG_DEL
MDSYS                          SDO_LRS_TRIG_UPD
MDSYS                          SDO_NETWORK_TRIG_DEL
MDSYS                          SDO_NETWORK_TRIG_UPD
MDSYS                          SDO_NETWORK_CONS_INS_TRIG
MDSYS                          SDO_NETWORK_CONS_DEL_TRIG
MDSYS                          SDO_NETWORK_CONS_UPD_TRIG
MDSYS                          SDO_NETWORK_UD_INS_TRIG
MDSYS                          SDO_NETWORK_UD_DEL_TRIG
MDSYS                          SDO_NETWORK_UD_UPD_TRIG

18 rows selected.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Piyush, June 14, 2007 - 11:30 am UTC

Thats great!!!

Please consider a small request... please make the trigger body type to clob (from long) so that probably we can search it simply in a select.

You are the BEST!!!

Piyush

Very neat, but seems to be some limitation

stephan, November 04, 2016 - 6:29 pm UTC

ERROR at line 3:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "USDCDBA.TRIGGER_TEXT_LIKE", line 10

This ran successfully for me specifying a schema that has 17 triggers, but not a schema that has 1403 triggers. Is it the number of triggers that's the limitation, or some other size?

Thanks!

November 06, 2016 - 1:05 am UTC

Its not the number of triggers, it is the limitation of the size of a single trigger. In this example, we're assuming it to be 32k or less.

Easier way (now that its 2016 - this question was first asked in 2007) is:

SQL> create table TRIGS as 
  2  select trigger_name, to_lob(TRIGGER_BODY) trig from user_triggers;

Table created.

and then just query TRIGS

Scanning triggers bodies and views in prod

MichaelH, July 25, 2018 - 1:35 am UTC

I need to scan trigger bodies in production but don't have access to create objects. Now that we are in 2018, how can I do this? :)

July 26, 2018 - 2:42 am UTC

Create a global temporary table in your own database, and a database link to production.

SQL> create global temporary table gtt on commit preserve rows
  2  as select trigger_name, to_lob(TRIGGER_BODY) trig from user_triggers@db122;

Table created.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here

Breadcrumb

Question and Answer