6
DBMS_DDL

This package provides access to some SQL Data Definition Language (DDL) statements from stored procedures. It also provides special administration operations that are not available as DDLs.

The ALTER_COMPILE and ANALYZE_OBJECT procedures commit the current transaction, perform the operation, and then commit again.

Requirements

This package runs with the privileges of calling user, rather than the package owner SYS.

Summary of Subprograms

Table 6-1 DBMS_DDL Package Subprograms

Subprogram	Description
ALTER_COMPILE procedure	Compiles the PL/SQL object.
ANALYZE_OBJECT procedure	Provides statistics for the database object.

ALTER_COMPILE procedure

This procedure is equivalent to the following SQL statement:

ALTER PROCEDURE|FUNCTION|PACKAGE [<schema>.] <name> COMPILE [BODY]

If the named object is this package, or any packages upon which it depends (currently, STANDARD or DBMS_STANDARD), then the procedure simply returns, and these packages are successfully compiled.

Syntax

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

Parameters

Table 6-2 ALTER_COMPILE Procedure Parameters

Parameter Description

Parameter	Description
type	Must be either `PROCEDURE`, `FUNCTION`, `PACKAGE`, `PACKAGE` `BODY` or `TRIGGER.`
schema	Schema name. If `NULL`, then use current schema (case-sensitive).
name	Name of the object (case-sensitive).

type

Must be either PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY or TRIGGER.

schema

Schema name.

If NULL, then use current schema (case-sensitive).

name

Name of the object (case-sensitive).

Exceptions

Table 6-3 ALTER_COMPILE Procedure Exceptions

Exception Description

Exception	Description
ORA-20000:	Insufficient privileges or object does not exist.
ORA-20001:	Remote object, cannot compile.
ORA-20002:	Bad value for object type Should be either `PACKAGE`, `PACKAGE BODY`, `PROCEDURE`, `FUNCTION`, or `TRIGGER`.

ORA-20000:

Insufficient privileges or object does not exist.

ORA-20001:

Remote object, cannot compile.

ORA-20002:

Bad value for object type

Should be either PACKAGE, PACKAGE BODY, PROCEDURE, FUNCTION, or TRIGGER.

ANALYZE_OBJECT procedure

This procedure provides statistics for the given table, index, or cluster. It is equivalent to the following SQL statement:

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

Syntax

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

Parameters

Table 6-4 ANALYZE_OBJECT Procedure Parameters

Parameter	Description
type	One of `TABLE`, `CLUSTER` or `INDEX`. If none of these, the procedure just returns.
schema	Schema of object to analyze. `NULL` means current schema, case-sensitive.
name	Name of object to analyze, case-sensitive.
method	One of `ESTIMATE`, `COMPUTE` or `DELETE`. If `ESTIMATE`, then either `estimate_rows` or `estimate_percent` must be non-zero.
estimate_rows	Number of rows to estimate.
estimate_percent	Percentage of rows to estimate. If `estimate_rows` is specified, then ignore this parameter.
method_opt	Method options of the following format. [ FOR TABLE ] [ FOR ALL [INDEXED] COLUMNS] [SIZE n] [ FOR ALL INDEXES ]
partname	Specific partition to be analyzed.

Exceptions

Table 6-5 ANALYZE_OBJECT Procedure Exceptions

Exception Description

Exception	Description
ORA-20000:	Insufficient privileges or object does not exist.
ORA-20001:	Bad value for object type. Should be either `TABLE`, `INDEX` or `CLUSTER`.
ORA-20002:	`METHOD` must be one of `COMPUTE`, `ESTIMATE` or `DELETE`.

ORA-20000:

Insufficient privileges or object does not exist.

ORA-20001:

Bad value for object type.

Should be either TABLE, INDEX or CLUSTER.

ORA-20002:

METHOD must be one of COMPUTE, ESTIMATE or DELETE.

6DBMS_DDL

Requirements

Summary of Subprograms

Table 6-1 DBMS_DDL Package Subprograms

ALTER_COMPILE procedure

Syntax

Parameters

Table 6-2 ALTER_COMPILE Procedure Parameters

Exceptions

Table 6-3 ALTER_COMPILE Procedure Exceptions

ANALYZE_OBJECT procedure

Syntax

Parameters

Table 6-4 ANALYZE_OBJECT Procedure Parameters

Exceptions

Table 6-5 ANALYZE_OBJECT Procedure Exceptions

6
DBMS_DDL