10.3.1.1 DBMS_DDL programs

Table 10-3 shows the programs defined in DBMS_DDL.

DBMS_DDL does not define any exceptions or nonprogram elements.

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:

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:

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

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

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

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

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

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.