Troubleshooting PL/SQL Code - Analyze and modify trigger state through views
(Page 2 of 4 )
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;
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.
Next: Recompiling Invalid Code >>
More Oracle Articles
More By O'Reilly Media
|
 This article is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O'Reilly; ISBN: 0596009771). Check it out today at your favorite bookstore. Buy this book now.
|
|
