Cheeky Cockatoo

SET SERVEROUTPUT ON
SET VERIFY OFF


ACCEPT search_string PROMPT "Enter the case-sensitive search string (max. 50 chars): "
PROMPT Please wait while the triggers are being searched ...

DECLARE 
  CURSOR c_triggers IS
    SELECT owner, trigger_name
    FROM dba_triggers;
  v_dummy INTEGER;
  v_cursor NUMBER;
  v_statement VARCHAR2(200);
  c_chunk_size CONSTANT INTEGER := 4000;
  v_chunk VARCHAR2(4000);
  v_chunk_length INTEGER;
  v_position INTEGER;
  v_offset INTEGER;
  v_search_string VARCHAR2(50);
  v_search_string_length INTEGER;
  v_cutoff_part VARCHAR2(100);    -- Double the size of the maximum search string
  v_found BOOLEAN;  -- Used just for better display (so the trigger details
                    -- wouldn't be displayed more than once when the search
                    -- string is found in more than one chunk of trigger body).
BEGIN 
  DBMS_OUTPUT.ENABLE(1000000);
  v_search_string := SUBSTR('&search_string', 1, 50);
  v_search_string_length := LENGTH(v_search_string);
  v_cursor := DBMS_SQL.OPEN_CURSOR;

  FOR v_trigger IN c_triggers LOOP
    v_statement := 'SELECT trigger_body FROM all_triggers' ||
                    ' WHERE owner = ''' || v_trigger.owner ||
                    ''' AND trigger_name = ''' || v_trigger.trigger_name || '''';
    DBMS_SQL.PARSE(v_cursor, v_statement, DBMS_SQL.NATIVE);
    DBMS_SQL.DEFINE_COLUMN_LONG(v_cursor, 1);
    v_dummy := DBMS_SQL.EXECUTE_AND_FETCH(v_cursor);

    -- Initialize variables before the inner loop:
    v_found := FALSE;
    v_cutoff_part := NULL;
    v_offset := 0;
    LOOP
      DBMS_SQL.COLUMN_VALUE_LONG(v_cursor, 1, c_chunk_size, v_offset,
                                 v_chunk, v_chunk_length);

      -- If it's not the 1st chunk, check the cut-off part of the trigger text
      -- (in the unlikely case that the chunk has been cut off exactly on the
      -- search string, in which case it wouldn't be found without this added
      -- piece of code):

      IF v_cutoff_part IS NOT NULL THEN
        v_cutoff_part := v_cutoff_part || SUBSTR(v_chunk, 1, v_search_string_length-1);
        IF INSTR(v_cutoff_part, v_search_string) > 0 THEN
          IF NOT v_found THEN
            -- Display the trigger details:
 
DBMS_OUTPUT.PUT_LINE('========================================');
            DBMS_OUTPUT.PUT_LINE('Owner: ' || v_trigger.owner || ', Trigger Name: '
                                 || v_trigger.trigger_name);
            v_found := TRUE;
          END IF;
          -- Display the found code:
 
DBMS_OUTPUT.PUT_LINE('----------------------------------------');
          DBMS_OUTPUT.PUT_LINE(v_cutoff_part || CHR(10));
        END IF;
      END IF;
      -- Check the current chunk of the trigger body:
      v_position := INSTR(v_chunk, v_search_string);
      IF v_position > 0 THEN
        IF NOT v_found THEN
          -- Display the trigger details:
 
DBMS_OUTPUT.PUT_LINE('========================================');
          DBMS_OUTPUT.PUT_LINE('Owner: ' || v_trigger.owner || ', Trigger Name: '
                               || v_trigger.trigger_name);
          v_found := TRUE;
        END IF;
 
DBMS_OUTPUT.PUT_LINE('----------------------------------------');
        IF v_position >= 20 THEN
        -- Display a piece of code around the search string:
          DBMS_OUTPUT.PUT_LINE(SUBSTR(v_chunk, v_position-20, 80) || CHR(10));
        ELSE
        -- Display a piece of code after the search string:
          DBMS_OUTPUT.PUT_LINE(SUBSTR(v_chunk, v_position, 80) || CHR(10));
        END IF;
      END IF;
      IF v_chunk_length < c_chunk_size THEN
        EXIT;
      END IF;
      v_offset := v_offset + v_chunk_length;
      v_cutoff_part := SUBSTR(v_chunk, -v_search_string_length+1);
    END LOOP;
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(v_cursor);
    RAISE;
END;
/