Finding Triggers Containing a Given Text String

Since the body of a trigger is declared as a long value instead of a varchar2 value, you can't search for strings in a trigger, such as trying to do "select owner,trigger_name from dba_triggers where trigger_body like '%sysdate%';"  to find all triggers containing the string "sysdate".   However, PL/SQL allows you to copy a long value into a varchar2 value, which can then be split up into individual lines (chr(10) is a line feed) and compared to the string you are looking for.  The following unnamed PL/SQL block can be used to find triggers with a given string.  Just substitute the string you are looking for in the "string_to_find" variable, and paste the code (except for the ending slash, which must be typed in separately after pasting and pressing the Enter key) into an sqlplus session (must be run from a dba account).   If you want to do an exact case match, change the line "IF upper(trig_line) like upper(string_to_find) THEN" to "IF trig_line like string_to_find THEN".  Similar code can be used to find any table's records with a long field matching a given string.

set serveroutput on;
DECLARE
-- Set the string to find, such as %mystring%, below, including wildcards
-- for the "like" match, such as "%" to match anything:
     string_to_find VARCHAR2(80) := '%mystring%';
     trig_owner VARCHAR2(30);
     trig_name VARCHAR2(30);
     trig_when VARCHAR2(2000);
     trig_desc VARCHAR2(2000);
     trig_long LONG;
     trig_long_2 VARCHAR2(32767);
     trig_line VARCHAR2(2000);
     trig_length NUMBER := 0;
     bgnstr NUMBER;
     endstr NUMBER;
     nxtstr NUMBER;
     occurs NUMBER;
     CURSOR trigger_sel IS
          SELECT owner,
               trigger_name,
               when_clause,
               description,
               trigger_body
          FROM sys.dba_triggers;
BEGIN
     dbms_output.enable(1000000);
     dbms_output.put_line('.');
     dbms_output.put_line('.');
     dbms_output.put_line('. Occurrences of "' || string_to_find || '" in triggers:');
     OPEN trigger_sel;
     LOOP
          FETCH trigger_sel INTO trig_owner, trig_name, trig_when, trig_desc, trig_long;
          EXIT WHEN trigger_sel%NOTFOUND;
          trig_long_2 := trig_long;
          bgnstr := 1;
          occurs := 0;
          endstr := LENGTH(trig_long_2);
          WHILE bgnstr <= endstr LOOP
               nxtstr := INSTR(trig_long_2,CHR(10),bgnstr);
               IF nxtstr = 0 THEN
                    nxtstr := endstr + 1;
               END IF;
               trig_line := SUBSTR(trig_long_2,bgnstr,nxtstr-bgnstr);
               IF upper(trig_line) like upper(string_to_find) THEN
                    occurs := occurs + 1;
                    IF occurs = 1 THEN
                         dbms_output.put_line('.');
                         dbms_output.put_line('. trigger: ' || trig_owner || '.' || trig_name);
                    END IF;
                    dbms_output.put_line('. text: ' || trig_line);
               END IF;
               bgnstr := nxtstr + 1;
          END LOOP;
     END LOOP;
     CLOSE trigger_sel;
     dbms_output.put_line('.');
END;
/

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.