Objetos de sistema y otros problemas

[avmm2004]

Encontre este tema para buscar textos en un trigger_body de Oracle:

Es un script completo, igual te vale:

Código SQL [-]


SET SERVEROUTPUT ON
SET VERIFY OFF


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

-- ----------------------------------------------------------
-- Name: search_triggers32.sql
-- Author: Dejan Jovanovic

-- Date: November 2002
-- Purpose: Search for the given text within a trigger body
-- Note: It can work only if the trigger body size is < 32K,
--       otherwise ORA-6502 is raised.
-- ----------------------------------------------------------
DECLARE
  CURSOR cur_triggers IS
    SELECT owner, trigger_name, trigger_body

    FROM all_triggers;
  v_position INTEGER;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
  FOR rec_trigger IN cur_triggers LOOP
    v_position := INSTR(rec_trigger.trigger_body, '&search_string');
    IF v_position > 0 THEN
      --  Display the trigger details:
      DBMS_OUTPUT.PUT_LINE('========================================');

      DBMS_OUTPUT.PUT_LINE('Owner: ' || rec_trigger.owner || ', Trigger
Name: ' || rec_trigger.trigger_name);
      IF v_position >= 20 THEN
        -- Display a piece of code around the searched string:
        DBMS_OUTPUT.PUT_LINE(SUBSTR(rec_trigger.trigger_body, v_position - 20, 80) || CHR(10));
      ELSE
        -- Display a piece of code after the searched string:
        DBMS_OUTPUT.PUT_LINE(SUBSTR(rec_trigger.trigger_body, v_position, 80) || CHR(10));
      END IF;
    END IF;

  END LOOP;
END;

..... el enlace es :

http://www.oracle.com/technology/ora...03/020903.html