The Data Dictionary: Make Views Work for YouBy Steven Feuerstein
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
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.
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:
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
Here are some examples of queries against USER_OBJECTS.
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
You can write queries against USER_SOURCE to
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
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
Here are some examples of queries against USER_PLSQL_OBJECT_SETTINGS.
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
Here are some examples of queries against USER_PROCEDURES.
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
Here are some examples of queries against USER_TRIGGERS.
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
Here are some examples of queries against USER_DEPENDENCIES.
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
Here are some examples of queries against USER_ARGUMENTS.
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.
Steven Feuerstein (firstname.lastname@example.org) 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.