Pictures of chickens, flowers, wheat, a boy looking through a magnifying glass, irrigation pipe, soybean pods, and fruits and vegetables.		Cooperative Extension Service
Cooperative Extension Service		Agricultural Experiment Station
Steve Rea's Oracle Tips, Tricks, and Scripts		Search | Publications | Jobs | Personnel Directory | Links

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;
/

This Page was Last Updated on 06/04/07

You Are Visitor Number

© 2006 University of Arkansas Division of Agriculture All rights reserved. Last Date Modified 02/09/2008 Webmaster
	University of Arkansas • Division of Agriculture Cooperative Extension Service 2301 South University Avenue Little Rock, Arkansas 72204 • USA Phone (501) 671-2000 • Fax (501) 671-2209	Mission • Disclaimer • EEO • Privacy • FOI