Analyze and modify trigger state through views - Oracle

Query the trigger-related views (USER_TRIGGERS, USER_TRIG_COLUMNS) to do any of the following:

1. Enable or disable all triggers for a given table. Rather than have to write this code manually, you can execute the appropriate DDL statements from within a PL/SQL program. See the section “Maintaining Triggers” in Chapter 19 for an example of such a program.
   
2. Identify triggers that execute only when certain columns are changed, but do not have a WHEN clause. A best practice for triggers is to include a WHEN clause to make sure that the specified columns actually have changed values (rather than simply writing the same value over itself).

Here is a query you can use to identify potentially problematic triggers lacking a WHEN clause:

  /* File on web: nowhen_trigger.sql */
  SELECT *
    FROM user_triggers tr
   WHERE when_clause IS NULL AND
         EXISTS (SELECT 'x'
                   FROM user_trigger_cols
                  WHERE trigger_owner = USER
                  AND trigger_name = tr.trigger_name);

Analyze argument information

A very useful view for programmers is USER_ARGUMENTS. It contains information about each of the arguments of each of the stored programs in your schema. It offers, simultaneously, a wealth of nicely parsed information about arguments and a bewildering structure that is very hard to work with.

Here is a simple SQL*Plus script to dump the contents of USER_ARGUMENTS for all the programs in the specified package:

  /* File on web: desctest.sql */
  SELECT object_name, argument_name, overload
       , POSITION, SEQUENCE, data_level, data_type
    FROM user_arguments
   WHERE package_name = UPPER ('&&1');

A more elaborate PL/SQL-based program for displaying the contents of USER_ ARGUMENTS may be found in the show_all_arguments.sp file on the book’s web site.

You can also write more specific queries against USER_ARGUMENTS to identify possible quality issues with your code base. For example, Oracle recommends that you stay away from the LONG datatype and instead use LOBs. In addition, the fixed-length CHAR datatype can cause logic problems; you are much better off sticking with VARCHAR2. Here is a query that uncovers the usage of these types in argument definitions:

  /* File on web: long_or_char.sql */
  SELECT object_name, argument_name, overload
       , POSITION, SEQUENCE, data_level, data_type
   FROM user_arguments
  WHERE data_type IN ('LONG','CHAR');

You can even use USER_ARGUMENTS to deduce information about a package’s program units that is otherwise not easily obtainable. Suppose that I want to get a list of all the procedures and functions defined in a package specification. You will say: “No problem! Just query the USER_PROCEDURES view.” And that would be a fine answer, except that it turns out that USER_PROCEDURES doesn’t tell you whether a program is a function or procedure (in fact, it can be both, depending on how the program is overloaded!).

You might instead, want to turn to USER_ARGUMENTS. It does, indeed, contain that information, but it is far less than obvious. To determine whether a program is a function or a procedure, you must check to see if there is a row in USER_ARGUMENTS for that package-program combination that has a POSITION of 0. That is the value Oracle uses to store the RETURN “argument” of a function. If it is not present, then the program must be a procedure.

The following function uses this logic to return a string that indicates the program type (if it is overloaded with both types, the function returns “FUNCTION,
PROCEDURE”). Note that the list_to_string function used in the main body is provided in the file.

  /* File on web: program_type.sf */
  CREATE OR REPLACE FUNCTION program_type (
     owner_in     IN   VARCHAR2
   , package_in   IN   VARCHAR2
   , program_in   IN   VARCHAR2
  )
     RETURN VARCHAR2
  IS
     TYPE overload_aat IS TABLE OF all_arguments.overload%TYPE
        INDEX BY PLS_INTEGER;

     l_overloads  overload_aat;
     retval       VARCHAR2 (32767);

  BEGIN
     SELECT  DECODE (MIN (POSITION), 0, 'FUNCTION', 'PROCEDURE')
     BULK COLLECT INTO l_overloads
         FROM all_arguments
        WHERE owner = owner_in
          AND package_name = package_in
          AND object_name = program_in
     GROUP BY overload;

     IF l_overloads.COUNT > 0
     THEN
        retval := list_to_string (l_overloads, ',', distinct_in => TRUE);
     END IF;

     RETURN retval;
  END program_type;
  /

Finally, you should also know that the built-in package, DBMS_DESCRIBE, provides a PL/SQL API to provide much of the same information as USER_ ARGUMENTS. There are differences, however, in the way these two elements handle datatypes.