Query the trigger-related views (USER_TRIGGERS, USER_TRIG_COLUMNS) to do any of the following:
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.
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;
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.