Home arrow Oracle arrow Page 2 - Troubleshooting PL/SQL Code

Analyze and modify trigger state through views - Oracle

In this second part of a nine-part series on managing PL/SQL code, you'll learn how to analyze argument information, recompile invalid code, and more. 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). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

  1. Troubleshooting PL/SQL Code
  2. Analyze and modify trigger state through views
  3. Recompiling Invalid Code
  4. Using Native Compilation
By: O'Reilly Media
Rating: starstarstarstarstar / 8
October 25, 2007

print this article



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 */
    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 */
     owner_in     IN   VARCHAR2
   , package_in   IN   VARCHAR2
   , program_in   IN   VARCHAR2
     TYPE overload_aat IS TABLE OF all_arguments.overload%TYPE

     l_overloads  overload_aat;
     retval       VARCHAR2 (32767);

     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
        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.

>>> More Oracle Articles          >>> More By O'Reilly Media

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: