DBMS_DDL, 2 of 2

Summary of Subprograms

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