As Published In
Oracle Magazine
November/December 2012

TECHNOLOGY: PL/SQL

  

The Data Dictionary: Make Views Work for You

By Steven Feuerstein Oracle ACE Director

 

Part 10 in a series of articles on understanding and using PL/SQL

If you’re reading this article, there’s a really good chance that you write PL/SQL code. Lots of it. Which means that you also will at least occasionally need to analyze that code, answering questions such as 

  • On which database objects does my program depend?  

  • Which of my packages contain calls to a subprogram in another package or a reference to a global variable?  

  • Do any of my subprograms contain parameters whose datatypes should not be used?  

  • Are all of my subprograms compiled with a sufficiently high level of optimization? 

You can, of course, always use the search feature of your editor or integrated development environment to look through multiple database objects and files to find specific chunks of text. But that won’t be enough to answer all of the above questions and many more you will encounter.

Don’t despair! One of the most lovely aspects of writing PL/SQL code and compiling that code into the database is that Oracle Database offers a set of views—known collectively as the data dictionary—that enable you to use the SQL and PL/SQL languages to get answers to just about any question you have about your code. Table 1 offers a high-level overview of the data dictionary views most often used to manage PL/SQL code.

USER_ARGUMENTS The arguments (parameters) in all the procedures and functions in your schema.
USER_DEPENDENCIES The dependencies to and from objects you own. This view is used mostly by Oracle Database to invalidate the status of database objects when an object on which they depend changes.
USER_ERRORS The current set of compilation errors for all stored objects (including triggers) you own. This view is accessed by the SHOW ERRORS SQL*Plus command. You can, however, write your own queries against it as well.
USER_IDENTIFIERS Introduced in Oracle Database 11g and populated by the PL/Scope compiler utility. Once populated, this view provides you with information about all the identifiers—program names, variables, and so on—in your code base.
USER_OBJECT_SIZE The size of the objects you own. Actually, this view shows you the source, parsed, and compile sizes for your code. Although it is used mainly by the compiler and runtime engine, you can use it to identify the large programs in your environment.
USER_OBJECTS The objects you own. You can, for instance, use this view to see if an object is marked INVALID, find all the packages that have EMP in their names, and so on.
USER_PLSQL_OBJECT_SETTINGS Information about the characteristics—such as the optimization level and debug settings—of a PL/SQL object that can be modified through the ALTER and SET DDL commands.
USER_PROCEDURES Information about stored programs, such as the AUTHID setting, whether the program was defined as DETERMINISTIC, and so on.
USER_SOURCE The text source code for all objects you own (in Oracle9i Database and above, including database triggers and Java source). This is a very handy view, because you can run all sorts of analyses of the source code against it with SQL and, in particular, Oracle Text.
USER_STORED_SETTINGS PL/SQL compiler flags. Use this view to discover which programs have been compiled via native compilation.
USER_TRIGGERS and USER_TRIGGER_COLS The database triggers you own (including the source code and a description of the triggering event) and any columns identified with the triggers, respectively. You can write programs against USER_TRIGGERS to enable or disable triggers for a particular table.

Table 1:
Useful views for PL/SQL programmers

This article explores many of the views in the table, describing the most useful columns in the views and offering examples of how you can put those views to use.

Data Dictionary Fundamentals

The data dictionary consists of numerous tables and views created by the database instance. User schemas generally have no privileges on these tables; Oracle Database grants only SELECT access on the views.

Most data dictionary views come in three versions: 

  1. The USER view: information about database objects owned by the schema to which you are connected  

  2. The ALL view: information about database objects to which the currently connected schema has access  

  3. The DBA view: unrestricted information about all the database objects in a database instance (non-DBA schemas usually have no authority to query DBA views) 

Let’s look at an example. Suppose I want to obtain a list of the objects—tables, views, packages, and so on—defined in the database.

The following query returns all the objects defined in my schema: 

SELECT * FROM user_objects

 

This query returns all the objects that are defined in my schema or for which I have been granted the privilege to use those objects in some way: 

SELECT * FROM all_objects

 

Finally, the following query returns a list of all the objects defined in the database instance—if I have the authority to select from the view: 

SELECT * FROM dba_objects

 

Usually the only difference between the USER view and the ALL view is that the latter contains one extra column, OWNER, that shows which schema owns the object.

The remainder of this article provides examples based on the USER view.

Display Information About Stored Objects

The USER_OBJECTS view contains a row for every database object owned by your schema. The most commonly used columns are 

  • OBJECT_NAME: Name of the object

  • OBJECT_TYPE: Type of the object, such as PACKAGE, FUNCTION, or TRIGGER

  • STATUS: Status of the object—VALID or INVALID

  • LAST_DDL_TIME: Time stamp indicating the last time this object was changed 

Here are some examples of queries against USER_OBJECTS. 

  • Show the names of all tables in my schema:  

    SELECT object_name
      FROM user_objects
     WHERE object_type = 'TABLE'
     ORDER BY object_name
    
  •   

  • Show the names of all objects whose status is invalid:  

    SELECT object_type, object_name
      FROM user_objects
     WHERE status = 'INVALID'
     ORDER BY object_type, object_name
    

     

    The status of a program unit (PL/SQL package, procedure, or function) is set to INVALID if a database object on which it depends is changed. That program unit must then be recompiled (which Oracle Database will often do automatically the next time you try to use that program unit).

  • Show all objects that have been changed today:  

    SELECT object_type, object_name, 
           last_ddl_time
      FROM user_objects
     WHERE last_ddl_time >= TRUNC (SYSDATE)
     ORDER BY object_type, object_name
    

 

Display and Search Source Code

All the program unit source code you’ve compiled into the database is accessible through the USER_SOURCE view, whose columns are 

  • NAME: Name of the object

  • TYPE: Type of the object (ranging from PL/SQL program units to Java source and trigger source)

  • LINE: Number of the line of the source code

  • TEXT: Text of the source code

You can write queries against USER_SOURCE to 

  • Find all the program units that call a particular subprogram of a package

  • Verify that coding standards are being followed

  • Find all occurrences of a literal value that needs to be changed

Here is an example: I need to change the parameter list and code of a procedure named CALC_TOTALS in the SALES_MGR package. I’d like to find out where this procedure is called, outside of the SALES_MGR package itself.  

SELECT name, line, text
  FROM user_source
 WHERE UPPER (text) 
  LIKE '%SALES_MGR.CALC_TOTALS%'
 ORDER BY name, line

 

Of course, this query will also find comments that contain this string, and there could be invocations of CALC_TOTALS that are not found, such as 

SALES_MGR.
CALC_TOTALS

 

Assuming, however, that you don’t write or format your code to break up subprogram calls like that, the query will do a pretty good job of identifying the places in your code you need to review.

And for an Oracle Database 11g instance, you could use the PL/Scope feature. See the “A Better USER_SOURCE” sidebar for more information.

Compiler Settings of Stored Code

The USER_PLSQL_OBJECT_SETTINGS view provides information about compiler settings of stored PL/SQL objects. Key columns are 

  • PLSQL_OPTIMIZE_LEVEL: Optimization level that was used to compile the object

  • PLSQL_CODE_TYPE: Compilation mode for the object

  • PLSQL_DEBUG: Whether or not the object was compiled for debugging

  • PLSQL_WARNINGS: Compiler warning settings that were used to compile the object

  • NLS_LENGTH_SEMANTICS: NLS length semantics that were used to compile the object

Here are some examples of queries against USER_PLSQL_OBJECT_SETTINGS.  

  • Find all the program units that are not taking sufficient advantage of compile time optimization in Oracle Database:

    SELECT name
      FROM user_plsql_object_settings
     WHERE plsql_optimize_level < 2
    

     

    An optimization level of 0 means no optimization at all. An optimization level of 1 means a minimal amount of optimization. Neither of these levels should be seen in a production environment.

  • Identify all programs for which compile time warnings (which provide feedback on the quality of your code) are disabled:  

    SELECT name, plsql_warnings
      FROM user_plsql_object_settings
     WHERE plsql_warnings LIKE '%DISABLE%';
    

 

Detailed Information About Procedures and Functions

The USER_PROCEDURES view provides information about all functions and procedures, both schema-level and those defined within packages, in your schema. Columns of this view are 

  • AUTHID: Shows whether a procedure or a function is defined as an invoker rights (CURRENT_USER) or definer rights (DEFINER) program unit

  • DETERMINISTIC: Set to YES if the function is defined to be deterministic, which theoretically means that the value returned by the function is determined completely by the function’s argument values

  • PIPELINED: Set to YES if the function is defined as a pipelined function, which means that it can be executed in parallel as part of a parallel query

  • OVERLOAD: Set to a positive number if this subprogram is overloaded, which means that there are at least two subprograms with this name in the same package

Here are some examples of queries against USER_PROCEDURES.  

  • Find all the procedures and functions that will run under invoker rights (the privileges of the invoker of the program are used at runtime to resolve references to database objects such as tables): 

    SELECT   object_name
           , procedure_name 
        FROM user_procedures
       WHERE authid = 'CURRENT_USER'
    ORDER BY object_name, procedure_name
    
  •   

  • Show all the functions declared to be deterministic:  

    SELECT   object_name
           , procedure_name 
        FROM user_procedures
       WHERE deterministic = 'YES'
    ORDER BY object_name, procedure_name
    

 

Analyze and Modify Trigger State

If you work with database triggers, USER_TRIGGERS, which contains a row for each trigger defined in your schema, will come in handy. Key columns are 

  • TRIGGER_NAME: The name of the trigger

  • TRIGGER_TYPE: A string that shows if this is a BEFORE or AFTER trigger and whether it is a row- or statement-level trigger (in a trigger that is fired before an INSERT statement, for example, the value of this column is BEFORE STATEMENT)

  • TRIGGERING_EVENT: The type of SQL operation—such as INSERT, INSERT OR UPDATE, DELETE OR UPDATE—that will cause the trigger to fire

  • TABLE_NAME: The name of the table on which the trigger is defined

  • STATUS: The status of the trigger—ENABLED or DISABLED

  • WHEN_CLAUSE: An optional clause you can use to avoid unnecessary execution of the trigger body

  • TRIGGER_BODY: The code executed when the trigger fires

Here are some examples of queries against USER_TRIGGERS. 

  • Find all disabled triggers: 

    SELECT *
      FROM user_triggers 
     WHERE status = 'DISABLED'
    
  •   

  • Find all row-level triggers defined on the EMPLOYEES table:  

    SELECT *
      FROM user_triggers 
     WHERE table_name = 'EMPLOYEES'
       AND trigger_type LIKE '%EACH ROW'
    
  •   

  • Find all triggers that fire when an UPDATE operation is performed:  

    SELECT *
      FROM user_triggers 
     WHERE triggering_event LIKE '%UPDATE%'
    

 

One limitation in the USER_TRIGGERS view is that the TRIGGER_BODY column type is LONG, which means that it cannot be used in a SQL comparison.

Suppose, for example, that I want to find all the triggers whose trigger body contains the string “emp”. The following query, unfortunately, fails and produces an ORA-00932 error: 

SELECT *
  FROM user_triggers
 WHERE trigger_body LIKE '%emp%'

 

So if you do want to search the contents of trigger bodies, you will need to use PL/SQL, in a block like this:

BEGIN FOR rec IN (SELECT * FROM user_triggers) LOOP IF rec.trigger_body LIKE '%emp%' THEN DBMS_OUTPUT.put_line ( 'Found in ' || rec.trigger_name); END IF; END LOOP; END;

Note that the USER_TRIGGER_COLS view keeps track of the columns that are referenced inside a trigger body.

Object Dependency Analysis

The USER_DEPENDENCIES view describes the dependencies between the procedures, packages, functions, package bodies, and triggers accessible to the current user. You can use it to perform impact analysis on your code, as in: How many programs will need to be changed if I change this table?

Key columns in this view are 

  • NAME: Name of the object

  • TYPE: Type of the object

  • REFERENCED_OWNER: Owner of the referenced object

  • REFERENCED_NAME: Name of the referenced object

  • REFERENCED_TYPE: Type of the referenced object 

Here are some examples of queries against USER_DEPENDENCIES. 

  • Find all the objects that depend on (reference) the EMPLOYEES table: 

    SELECT type, name 
       FROM user_dependencies
      WHERE  referenced_name = 'EMPLOYEES'
    ORDER BY type, name
    
  •   

  • Find all the objects in the current schema on which the ORDER_MGR package depends: 

    SELECT referenced_type
         , referenced_name 
        FROM user_dependencies
       WHERE name = 'ORDER_MGR'
         AND referenced_owner = USER
    ORDER BY referenced_type, 
             referenced_name
    

 

A best practice that I, and others, strongly recommend is to avoid repeating SQL statements by “hiding” those statements inside a procedure or a function. Let’s look at an example and then at how the USER_DEPENDENCIES view can help us identify violations of this best practice.

It is very common in PL/SQL code to find many queries that retrieve a single row for a primary key. Here’s a PL/SQL example with a query that uses the standard Oracle Database EMPLOYEES table: 

PROCEDURE process_employee (
   employee_id_in IN INTEGER)
IS
   l_name   VARCHAR2 (100);
BEGIN
   SELECT last_name
     INTO l_name
     FROM employees
    WHERE employee_id = employee_id_in;
END;

 

Instead of writing this query each time, I suggest writing a function once that contains this query and returns the desired value. Then you can call the function as needed. Assuming that I have created a package named EMPLOYEES_API with a function named LAST_NAME, the above procedure can be changed to 

PROCEDURE process_employee (
   employee_id_in IN INTEGER)
IS
   l_name   VARCHAR2 (100);
BEGIN
   l_name := 
      employees_api.
        last_name (employee_id_in);
END;

 

Now if I ever need to change the query for any reason (such as to take advantage of Oracle Database 11g’s function result cache feature), I’ll be able to make the change in one place, rather than having to find all occurrences of the query in my application code.

So suppose my development team has added this best practice to its coding standards: the only PL/SQL program units that should contain SQL statements are packages that end with the suffix _API.

I can then write a query against USER_DEPENDENCIES that identifies all program units that violate this rule: 

SELECT name,
       TYPE,
       referenced_owner,
       referenced_name
  FROM user_dependencies
 WHERE     TYPE IN
              ('PACKAGE',
               'PACKAGE BODY',
               'PROCEDURE',
               'FUNCTION',
               'TRIGGER',
               'TYPE')
   AND referenced_type = 'TABLE'
   AND name NOT LIKE '%\_API' ESCAPE '\'
ORDER BY name
       , referenced_owner
       , referenced_name

 

Analyze Argument Information

USER_ARGUMENTS is a very useful view for PL/SQL programmers. It contains information about the arguments (also known as parameters) of each of the stored programs in your schema. It offers, simultaneously, a wealth of nicely parsed information and a complex structure. Key columns are 

  • OBJECT_NAME: The name of the procedure or function

  • PACKAGE_NAME: The name of the package in which the procedure or function is defined

  • ARGUMENT_NAME: The name of the argument

  • POSITION: The position of the argument in the parameter list (if 0, this is the RETURN clause of a function)

  • IN_OUT: The mode of the argument—IN, OUT, or IN OUT

  • DATA_TYPE: The datatype of the argument

  • DATA_LEVEL: The nesting depth of the argument for composite types (for example, if one of your arguments’ datatypes is a record, USER_ARGUMENTS will have a row for this argument with a DATA_LEVEL of 0 and then a row for each field in the record with a DATA_LEVEL of 1) 

Here are some examples of queries against USER_ARGUMENTS. 

  • Find all programs that have an argument of type LONG. This is the datatype that was used to store large strings (more than 4,000 characters) in past versions of Oracle Database. Now the database uses large object types such as character large object (CLOB). Oracle recommends that any usages of LONG be converted to CLOB. USER_ARGUMENTS makes it easy to find all such usages in parameter lists:  

    SELECT object_name
         , package_name
         , argument_name
      FROM user_arguments
     WHERE data_type = ‘LONG’
    
  •  

     

    Next Steps 


     DOWNLOAD Oracle Database 11g

     TEST your PL/SQL knowledge

     READ PL/SQL 101, Parts 1-9  

     READ more about READ more about the data dictionary

  • Find all functions that have an OUT or an IN OUT argument. A recommendation you will hear from many programming experts is that functions should contain only IN arguments. A function with an OUT or an IN OUT argument cannot be called inside a SQL statement, and it cannot be used in a function-based index. If you need to return multiple pieces of information, use a procedure or return a record. Listing 1 demonstrates a query that will identify all functions defined in packages that violate this best practice. 

    Code Listing 1: Find functions that have an OUT or an IN OUT argument 

    1  SELECT ua.object_name,
     2         ua.package_name,
     3         ua.argument_name,
     4         ua.in_out
     5    FROM (SELECT *
     6            FROM user_arguments
     7           WHERE position = 0) funcs,
     8         user_arguments ua
     9   WHERE     ua.in_out IN ('OUT', 'IN OUT')
    10         AND ua.position > 0
    11         AND ua.data_level = 0
    12         AND funcs.object_name = ua.object_name
    13         AND funcs.package_name = ua.package_name
    14         AND (   funcs.overload = ua.overload
    15              OR (funcs.overload IS NULL
    16                   AND ua.overload IS NULL))
    

     

    Lines Description
    5–7 I use an inline view in the FROM clause to identify all those rows in USER_ARGUMENTS that are RETURN clauses (and therefore identify functions).
    9–11 I look for OUT or IN OUT arguments that are not in RETURN clauses and are not “nested” information, such as fields of a record argument.
    12–16 I use this rather lengthy join condition between the inline view (abbreviated as “funcs”) and USER_ARGUMENTS. The object names and package names must match, and the overload value must be the same or both must be NULL. The overload column is not NULL if the package has two or more subprograms with the same name.

 

It’s a Gold Mine in There

This article merely scratches the surface of the application information that can be mined from the data dictionary views in Oracle Database. PL/SQL editors such as Oracle SQL Developer provide user interfaces to many of these views, making it easier to browse their contents.

A Better USER_SOURCE 


You can execute queries against USER_SOURCE to check for the presence or absence of certain strings and thus perform some fairly simplistic quality assurance checks of your code.  

But Oracle offers a much more powerful means of analyzing your source code in Oracle Database 11g: with PL/Scope.

PL/Scope is a tool invoked by the PL/SQL compiler to collect information about all the identifiers (variables, procedures, functions, types, and so on) in your PL/SQL program unit and make it available through the USER_IDENTIFIERS view. It makes it relatively easy to get answers to questions that would otherwise require you to parse a PL/SQL program unit and then analyze the parse tree.

Here’s one example: My manager has asked me to remove from our programs any variables, constants, exceptions, and the like that are declared but never used. Finding all candidates for removal by simply searching code would be both time-consuming and error-prone.

With PL/Scope, it’s easy. USER_IDENTIFIERS contains a row for each declaration of an identifier. It may also contain other rows for usages of that identifier: a reference to it or a line of code that changes its value.

So a MINUS operation between these two sets of rows will leave us with all those identifiers that are declared but never referenced or used. Here’s the query for exceptions: 

WITH subprograms_with_exception
        AS (SELECT DISTINCT owner
                          , object_name
                          , object_type
                          , name
              FROM all_identifiers has_exc
             WHERE     has_exc.owner = USER
                   AND has_exc.usage = 'DECLARATION'
                   AND has_exc.TYPE = 'EXCEPTION'),
     subprograms_with_raise_handle
        AS (SELECT DISTINCT owner
                          , object_name
                          , object_type
                          , name
              FROM all_identifiers with_rh
             WHERE     with_rh.owner = USER
                   AND with_rh.usage = 'REFERENCE'
                   AND with_rh.TYPE = 'EXCEPTION')
SELECT *
  FROM subprograms_with_exception
MINUS
SELECT *
  FROM subprograms_with_raise_handle

 

PL/Scope is a powerful, flexible utility that can have a big impact on your ability to analyze code and identify ways to improve it. I wrote at more length about PL/Scope in 2010 in “Zoom In on Your Code”.

Take the Challenge


Each PL/SQL 101 article offers a quiz to test your knowledge of the information provided in it. The quiz appears below and also at PL/SQL Challenge, a Website that offers online quizzes on the PL/SQL language as well as SQL and Oracle Application Express. 

Here is your quiz for this article:
Assume that all the packages in my schema contain at least one subprogram (procedure or function). Which of these queries will display the names of all the packages in my schema?


a. 
  SELECT object_name
    FROM user_objects
   WHERE object_type = 'PACKAGE'
ORDER BY object_name
/

 

b.

  SELECT package_name
    FROM user_procedures
   WHERE package_name IS NOT NULL
ORDER BY package_name
/

 

c.

  SELECT DISTINCT object_name
    FROM user_procedures
   WHERE procedure_name IS NOT NULL
ORDER BY object_name
/

 

d.

  SELECT DISTINCT name
    FROM user_dependencies
   WHERE TYPE = 'PACKAGE'
/

 


Steven Feuerstein Headshot

Steven Feuerstein (steven.feuerstein@quest.com) is Quest Software’s PL/SQL evangelist. He has published 10 books on Oracle PL/SQL (O’Reilly Media) and is an Oracle ACE Director. More information is available at stevenfeuerstein.com.

 







Send us your comments