[Chapter 10] 10.3 DBMS_DDL: Compiling and Analyzing Objects

10.3 DBMS_DDL: Compiling and Analyzing Objects

The DBMS_DDL package provides access from within PL/SQL to two DDL (Data Definition Language) statements. It also offers special administrative services that are not available through DDL syntax (Oracle8 only).

10.3.1 Getting Started with DBMS_DDL

This DBMS_DDL package is created when the Oracle database is installed. The dbmsdesc.sql script (found in the built-in packages source code directory, as described in Chapter 1 ) contains the source code for this package's specification. This script is called by catproc.sql , which is normally run immediately after database creation. The script creates the public synonym DBMS.DDL for the package and grants EXECUTE privilege on the package to public. All Oracle users can reference and make use of this package.

DBMS_DDL programs "run as user," which means that they execute with the privileges of the user who calls that program.

NOTE: All programs in DBMS_DDL first commit the current transaction, then perform the specified operation. When the operation is completed, another commit is performed.

10.3.1.1 DBMS_DDL programs

Table 10-3 shows the programs defined in DBMS_DDL.

Table 10.3: DBMS_DDL Programs
Name	Description	Use in SQL
ALTER_COMPILE	Compiles the specified PL/SQL object.	No
ANALYZE_OBJECT	Computes statistics for the specified database object.	No
REFERENCEABLE	Makes the specified table referenceable for object identifiers (OIDs) in existing data structures. Available in Oracle8 only.	No
ALTER_TABLE_NOT_REFERENCEABLE	Undoes the action of the previous procedure, ALTER_TABLE_REFERENCEABLE. Available in Oracle8 only.	No

DBMS_DDL does not define any exceptions or nonprogram elements.

10.3.2 Compiling PL/SQL Objects

You can recompile PL/SQL objects that are already stored in the database by calling the ALTER_COMPILE procedure.

10.3.2.1 The DBMS_DDL.ALTER_COMPILE procedure

Here's the header for this procedure:

PROCEDURE DBMS_DDL.ALTER_COMPILE
   (type IN VARCHAR2
   ,schema IN VARCHAR2
   ,name IN VARCHAR2);

Here are the possible values you can provide for the type parameter (enclosed in single quotes when you pass them to the procedure) and the actions that result:

Type	Action
PROCEDURE	Recompiles the specified procedure
FUNCTION	Recompiles the specified function
PACKAGE	Recompiles the specified package specification and body
PACKAGE BODY	Recompiles the specified package body
PACKAGE SPECIFICATION	Recompiles the specified package specification

The schema and name arguments are case-sensitive. In almost every instance, the names of your PL/SQL objects are stored in uppercase (you must enclose those names in double quotes when creating the objects if you want mixed case). You will therefore need to specify the names in uppercase when you call ALTER_COMPILE.

Note the following about using this package:

If you pass NULL for the schema, then the current schema (the same value returned by a call to the built-in function USER) will be used.
If you try to recompile DBMS_DDL, STANDARD, or DBMS_STANDARD (assuming that you have the privileges to do so), this procedure will return without taking any action.
When you request recompilation of a program, Oracle will first recompile any objects upon which that program depends, and which are marked invalid.

In order to compile a program, you must own that program (in other words, the schema you specify is the owner of the program for which you request compilation) or your schema must have been granted the ALTER ANY PROCEDURE privilege to compile another schema's programs.

The following command from a DBA account in SQL*Plus enables the SCOTT account to compile the programs of other schemas:

SQL> GRANT ALTER ANY PROCEDURE TO SCOTT;

Here are a few examples of usage, assuming that SCOTT has been granted the ALTER ANY PROCEDURE privilege:

Recompile the procedure in the SCOTT schema that shows employees.
```
    SQL> exec DBMS_DDL.ALTER_COMPILE ('PROCEDURE', USER, 'SHOWEMPS');
```
Notice that I pass in all arguments in uppercase to ensure a match.
Recompile the body of the empmaint package in the SALLY schema.
```
    SQL> exec DBMS_DDL.ALTER_COMPILE ('PACKAGE BODY', 'SALLY, 'EMPMAINT');
```
Here too, I pass in all arguments in uppercase to ensure a match.

Suppose that I had created a procedure as follows:
```
    CREATE OR REPLACE PROCEDURE "%$^abc" IS
    BEGIN
     ...
    END;
    /
```
(Try it! You will discover that this syntax will be acceptable to the compiler! It turns out that if you enclose your identifier -- be it a table name, column name, program name or variable name -- in double quotes, all of the normal rules are suspended.)

Then I can recompile this program with the following command:
```
    SQL> exec DBMS_DDL.ALTER_COMPILE ('PROCEDURE', USER, '%$^abc');
```
Strange, but true!

10.3.2.1.1 Exceptions

The ALTER_COMPILE procedure may raise any of the following exceptions:

ORA-20000: Insufficient privileges or object does not exist. You must either own the specified object or be granted the CREATE ANY PROCEDURE privilege.
ORA-20001: Remote object, cannot compile. You can only recompile objects on the local database instance.
ORA-20002: Bad value for object type. You need to provide one of the values listed in the previous section.

Notice that these exceptions are not defined in the specification of the package. Instead, ALTER_COMPILE simply calls RAISE_APPLICATION_ERROR with one of the above error numbers. These error numbers may therefore conflict with your own -20NNN error number usages. If you embed calls to ALTER_COMPILE inside your application or utility, watch out for the confusion such conflicts can cause.

10.3.2.1.2 Example

At first glance, you might say this of the ALTER_COMPILE procedure: "Why bother? The command is available in SQL*Plus. I'll just execute the ALTER PROCEDURE XXX COMPILE command when I need to recompile."

The big difference between that command and the ALTER_COMPILE procedure, of course, is that you can run the latter within a PL/SQL block or program. This allows you to apply the full power and flexibility of a procedural language to make the utility more useful. This technique is demonstrated by my recompile procedure, which follows. This program recompiles all stored PL/SQL objects that are identified by the parameters you provide to it.

/* Filename on companion disk: 

recmpile.sp */*
CREATE OR REPLACE PROCEDURE

 recompile 
   (status_in IN VARCHAR2 := 'INVALID',
    name_in IN VARCHAR2 := '%',
    type_in IN VARCHAR2 := '%',
    schema_in IN VARCHAR2 := USER)
IS
   v_objtype VARCHAR2(100);

   CURSOR obj_cur IS   
      SELECT owner, object_name, object_type
        FROM ALL_OBJECTS
       WHERE status LIKE UPPER (status_in)
         AND object_name LIKE UPPER (name_in)
         AND object_type LIKE UPPER (type_in)
         AND owner LIKE UPPER (schema_in)
       ORDER BY 
         DECODE (object_type, 
            'PACKAGE', 1, 
            'FUNCTION', 2, 
            'PROCEDURE', 3,
            'PACKAGE BODY', 4);
BEGIN
   FOR rec IN obj_cur
   LOOP
      IF rec.object_type = 'PACKAGE'
      THEN
         v_objtype := 'PACKAGE SPECIFICATION';
      ELSE
         v_objtype := rec.object_type;
      END IF;  

      DBMS_DDL.ALTER_COMPILE (v_objtype, rec.owner, rec.object_name); 

      DBMS_OUTPUT.PUT_LINE 
         ('Compiled ' || v_objtype || ' of ' || 
          rec.owner || '.' || rec.object_name);  
   END LOOP;
END;
/

Here are a few interesting aspects to this procedure:

The default values are set up so that if you call recompile without any arguments, it will recompile all objects marked INVALID in your own schema. That seemed to be the most common usage for this utility, so why not design the interface to make that the easiest usage as well?
I translate the PACKAGE type to PACKAGE SPECIFICATION. I also order the objects retrieved so that they are compiled in the following order: package, function, procedure, and finally package body. I do this so that package specifications are compiled first, putting in place as many of the interfaces as possible, before moving on to the implementations of standalone programs and then package bodies.
It was necessary to declare a local variable for the object type because the definition of the OBJECT_TYPE column in ALL_OBJECTS is as VARCHAR2(12). When I combine PACKAGE and SPECIFICATION into a single "type" for purposes of the recompilation, the new type is too big for the record-based field.
I call DBMS_OUTPUT.PUT_LINE to provide a "trace" of the recompilations that have taken place.
You can use wildcarded values in just about all of the arguments (name, type, owner) in order to perform just those recompilations that you need to perform.
I apply the UPPER function to all arguments. This will work (and make it easier to specify objects for recompilation) unless you use mixed case in your object names.

So to recompile all invalid programs in my schema, I would enter this command:

SQL> exec recompile

To recompile only package bodies that are invalid, I would execute the following:

SQL> exec recompile (type_in => 'PACKAGE BODY')

10.3.3 Computing Statistics for an Object

Use the ANALYZE_OBJECT procedure to compute statistics for the specified table, index, or cluster.

10.3.3.1 The DBMS_DDL. ANALYZE_OBJECT procedure

Here is the header for this procedure:

PROCEDURE DBMS_DDL.ANALYZE_OBJECT
   (type IN VARCHAR2
   ,schema IN VARCHAR2
   ,name IN VARCHAR2
   ,method IN VARCHAR2
   ,estimate_rows IN NUMBER DEFAULT NULL
   ,estimate_percent IN NUMBER DEFAULT NULL
   ,method_opt IN VARCHAR2 DEFAULT NULL);

Parameters are summarized in the following table.

Parameter	Description
type	The type of the database object. Legal values are TABLE, CLUSTER, or INDEX.
schema	The name of the schema containing the object for which you wish to compute statistics. If NULL, then the current schema is used.
name	The name of the object for which you wish to compute statistics.
method	Action to be taken by the program. ESTIMATE, DELETE, and COMPUTE are accepted values and are explained following.
estimate_rows	The number of rows to be used to perform the statistics estimate. Cannot be less than 1. Used only if method is ESTIMATE.
estimate_percent	The percentage of rows to be used to perform the statistics estimate. Ignored if estimate_rows is non-NULL. Must be between 1 and 99. Used only if method is ESTIMATE.
method_opt	The method option, indicating which elements of the object will be analyzed.

The schema and name arguments are case-sensitive. In almost every instance, the names of your PL/SQL objects are stored in uppercase. (Enclose those names in double quotes when creating the objects if you want mixed case). You will therefore need to specify the names in uppercase when you call COMPUTE_STATISTICS.

This procedure offers a procedural equivalent to the SQL DDL statement:

ANALYZE TABLE|CLUSTER|INDEX [<schema>.]<name>
   [<method>] STATISTICS [SAMPLE <n> [ROWS|PERCENT]]

If the type you specify is not one of TABLE, CLUSTER, or INDEX, the procedure returns without taking action.

NOTE: The type is not case-sensitive; it is always converted to uppercase.

Here are the valid entries for the method argument, and the resulting activity. (Remember that when you pass one of these values, they must be enclosed in single quotes.)

COMPUTE: Exact statistics are computed based on the entire contents of the specified object. These values are then placed in the data dictionary.
ESTIMATE: Statistics are estimated. With this option, either estimate_rows or estimate_percent must be non-NULL. These values are then placed in the data dictionary.
DELETE: The statistics for this object are deleted from the data dictionary.

Here are the valid method_opt entries and the resulting impact:

FOR TABLE: Collects statistics for the table.
FOR ALL COLUMNS [SIZE N]: Collects column statistics for all columns and scalar attributes. The size is the maximum number of partitions in the histogram, with a default of 75 and a maximum of 254.
FOR ALL INDEXED COLUMNS [SIZE N}: Collects column statistics for all indexed columns in the table. The size is the maximum number of partitions in the histogram, with a default of 75 and a maximum of 254.
FOR ALL INDEXES: Collects statistics for all indexes associated with the table.

10.3.3.1.1 Exceptions

DBMS_DDL.ANALYZE_OBJECT may raise any of the following exceptions:

ORA-20000: Insufficient privileges or object does not exist. You must either own the specified object or be granted the CREATE ANY PROCEDURE privilege.
ORA-20001: Bad value for object type. You must specify TABLE, INDEX, or CLUSTER.

Notice that these exceptions are not defined in the specification of the package. Instead, ANALYZE_OBJECT simply calls RAISE_APPLICATION_ERROR with one of the above error numbers. These error numbers may therefore conflict with your own -20NNN error number usages. If you embed calls to ANALYZE_OBJECT inside your application or utility, watch out for the confusion such conflicts can cause.

10.3.4 Setting Referenceability of Tables

When you create an object table, it automatically becomes referenceable, unless you use the OID AS clause when creating the table. The OID AS clause allows you to create an object table and to assign to the new table the same embedded object ID (EOID) as another object table of the same type. After you create a new table using the OID AS clause, you end up with two object tables with the same EOID; the new table is not referenceable, the original one is. All references that previously pointed to the objects in the original table still reference the same objects in the same original table. If you execute the ALTER_TABLE_REFERENCEABLE procedure of the DBMS_DDL package on the new table, it will make that table the referenceable table replacing the original one. Any references will then point to the objects in the new table instead of to the objects in the original table.

With DBMS_DDL, available only in Oracle8, you can both make a table referenceable and reverse that step.

10.3.4.1 The DBMS_DDL. (Oracle8 only)

To make a table referenceable, call the following procedure:

PROCEDURE DBMS_DDL.ALTER_TABLE_REFERENCEABLE
   (table_name IN VARCHAR2
   ,table_schema IN VARCHAR2 DEFAULT NULL
   ,affected_schema IN VARCHAR2 DEFAULT NULL);

Parameters are summarized in the following table.

Parameter	Description
table_name	The name of the table to be made referenceable. You cannot use a synonym. The argument is case-sensitive.
table_schema	The schema containing the table to be made referenceable. If NULL, then the current schema is used. The argument is case-sensitive.
affected_schema	The schema that is to be affected by this change. If NULL, then the PUBLIC schema is used. In other words, the change takes effect in all schemas. The argument is case-sensitive.

This program alters the specified object table, table_schema.table_name, so it becomes the referenceable table for the specified schema, affected_schema. This program is the equivalent of the following SQL statement,

ALTER TABLE [<table_schema>.]<table_name>
   REFERENCEABLE FOR <affected_schema>

which is currently neither supported nor available as a DDL statement. You can obtain this effect only through a call to the ALTER_TABLE_REFERENCEABLE built-in package.

Notice that each argument to this program is case-sensitive. ALTER_TABLE_REFERENCEABLE will not automatically convert to uppercase the table name you pass to it. You must make sure that the table and schema names you provide match exactly the case of the objects found inside the database.

You will be able to execute this procedure successfully only if you have the appropriate privileges. Here are some rules to keep in mind:

The user who executes this procedure must own the new table, and the affected schema must be the same as the user or PUBLIC; alternatively, the user must have ALTER ANY TABLE, SELECT ANY TABLE, and DROP ANY TABLE privileges.
If the affected schema is PUBLIC (and the user does not have ALTER ANY TABLE, SELECT ANY TABLE, and DROP ANY TABLE privileges), then the user must own the old mapping table (the table upon which the new table is based) for PUBLIC as well.

One tricky aspect to the way this built-in is defined: the default affected_schema is PUBLIC. Combine this fact with the previous rules, and you discover that the simplest form of calling the built-in (providing only the table name),

EXECUTE DBMS_DDL.ALTER_TABLE_REFERENCEABLE ('EMPOBJTAB');

will quite often fail with this error:

ORA-20000: insufficient privileges, invalid schema name or 
           table does not exist

You will say to yourself, "But I own the table!" And then you will realize, "Oh, if I don't specify the affected schema, then PUBLIC is used and I do not have ALTER ANY TABLE or any of the other privileges needed." So all I can really do is this,

EXECUTE DBMS_DDL.ALTER_TABLE_REFERENCEABLE ('EMPOBJTAB', USER, USER);

and make sure that the command is applied only to my schema.

10.3.4.1.1 Example

Generally, you will use the ALTER_TABLE_REFERENCEABLE procedure when you want to replace an existing object table with a new table of the same structure. In this scenario, you will want to make sure that all EOIDS point to this new table.

Here are the steps you would take to use ALTER_TABLE_REFERENCEABLE to make this "switch." (These steps are collected together using an employee table as an example in tabref.sql on the companion disk.)

First, you must have an object table already in place. Here is the code to create an employee object and then an object table for that object:
```
CREATE TYPE empobj AS OBJECT (ename VARCHAR2(100), empno INTEGER);
/
CREATE TABLE emp OF empobj
/
```
Now I will create a new object table based on the same object as the original table like this:
```
CREATE TABLE empNew OF empobj OID AS emp
/
```
Next, I transfer the contents of the original emp table to the empNew table. In Oracle8, unique object identifiers are automatically assigned to objects when they are stored as "table objects." It turns out that this identifier is stored in a hidden 16-byte RAW field, SYS_NC_OID$. This ID or OID can be referenced from columns in other tables, much like a foreign key. I use this in my INSERT to make sure the new table has the same identifier as the old. I also must use aliases for my table names for this process to work correctly.
```
INSERT INTO empNew en (SYS_NC_OID$, en)
  SELECT SYS_NC_OID$, VALUE (eo) FROM emp eo
/
```
Now I make the new table referenceable. Notice that the name of the table is passed in upper case and I explicitly pass USER as the schema to avoid the inadvertent usage of PUBLIC.
```
EXECUTE DBMS_DDL.ALTER_TABLE_REFERENCEABLE ('EMPNEW', USER, USER);
```
For my final trick, I swap the names of the tables so that I end up with a new emp table.
```
RENAME emp TO empOld;
RENAME empNew TO emp;
```

10.3.4.1.2 Exceptions

The ALTER_TABLE_REFERENCEABLE procedure may raise the following exception:

ORA-20000: Insufficient privileges; invalid schema name, or table does not exist.

Notice that this exception is not defined in the specification of the package. Instead, this program simply calls RAISE_APPLICATION_ERROR with the previous error number. This error number may therefore conflict with your own -20NNN error number usages. If you embed calls to this procedure inside your application or utility, watch out for the confusion such a conflict can cause.

10.3.4.2 The DBMS_DDL. ALTER_TABLE_NOT_REFERENCEABLE procedure (Oracle8 only)

For the affected schema, this procedure simply reverts to the default table referenceable for PUBLIC; that is, it simply undoes the previous ALTER_TABLE_REFERCEABLE call for this specific schema. The header follows:

PROCEDURE DBMS_DDL.ALTER_TABLE_NOT_REFERENCEABLE
   (table_name IN VARCHAR2
   ,table_schema IN VARCHAR2 DEFAULT NULL
   ,affected_schema IN VARCHAR2 DEFAULT NULL);

Parameters are summarized in the following table.

Parameter	Description
table_name	The name of the table to be made nonreferenceable. You cannot use a synonym. The argument is case-sensitive.
table_schema	The schema containing the table to be made nonreferenceable. If NULL, then the current schema is used. The argument is case-sensitive.
affected_schema	The schema that is to be affected by this change. If NULL, then the current schema is used. PUBLIC may not be specified. The argument is case-sensitive.

This procedure is equivalent to the following SQL statement,

ALTER TABLE [<table_schema>.]<table_name>
   NOT REFERENCEABLE FOR <affected_schema>

which is currently neither supported nor available as a DDL statement.

10.3.4.2.1 Exceptions

The ALTER_TABLE_NOT_REFERENCEABLE procedure may raise the following exception:

ORA-20000: Insufficient privileges; invalid schema name or table does not exist.

Notice that this exception is not defined in the specification of the package. Instead, this program simply calls RAISE_APPLICATION_ERROR with the preceding error number. This error number may therefore conflict with your own -20NNN error number usages. If you embed calls to this procedure inside your application or utility, watch out for the confusion such conflicts can cause.