2

I'd like to perform a validation by capturing all my triggers that call procedures.To do the search, I'm looking for the words "Call" and "Execute" in the trigger body

-- I'm using the following query:
SELECT
   OWNER,
   TRIGGER_NAME
FROM
   DBA_TRIGGERS
WHERE
   DBMS_LOB.INSTR(TRIGGER_BODY, 'CALL') > 0
      OR DBMS_LOB.INSTR(TRIGGER_BODY, 'EXECUTE') > 0;

But I'm getting an error: ORA-00997: invalid use of data type LONG

How can I make this query?

1 Answer 1

4

There's no need to search the text. You can leverage Oracle's dependency registration instead:

SELECT *
  FROM dba_dependencies
 WHERE type = 'TRIGGER'
   AND referenced_name NOT IN ('DBMS_STANDARD','STANDARD')
   AND referenced_type IN ('PROCEDURE','FUNCTION','PACKAGE')

If you did want to search the trigger_body content, because it's a long which has so many restrictions its nearly unusable, you'd need to either write it to a new table as a CLOB or use dbms_sql.column_value_long to progressively build a temporary CLOB. But that's rather complex and requires pages of code, something only advanced programmers would attempt. Further, EXEC is SQL*Plus syntax and CALL is a SQL statement. Neither are used within the PL/SQL language that triggers use. Procedures are invoked simply by naming them. Trying to parse text and look for procedure calls is like rewriting Oracle's PL/SQL parsing engine. They've already done that work for you at compile time and exposed the dependency chain through the dba_dependencies view.

1
  • Good morning, Paul. Thanks for the explanations, using the query you provided I got the result I expected for my purposes. Thank you for your attention. Nov 30, 2023 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.