Summary of DBMS_DDL Subprograms

Table 9-1 DBMS_DDL Package Subprograms

Subprogram	Description
ALTER_COMPILE Procedure	Compiles the PL/SQL object.
ANALYZE_OBJECT Procedure	Provides statistics for the database object.
IS_TRIGGER_FIRE_ONCE Function	Returns TRUE if the specified DML or DDL trigger is set to fire once. Otherwise, returns FALSE.
SET_TRIGGER_FIRING_PROPERTY Procedure	Sets the specified DML or DDL trigger's firing property.
ALTER_TABLE_REFERENCEABLE Procedure	Reorganizes object tables and swizzles references
ALTER_TABLE_NOT_REFERENCEABLE Procedure	Reorganizes object tables and swizzles references

ALTER_COMPILE Procedure

This procedure is equivalent to the following SQL statement:

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

Syntax

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

Parameters

Table 9-2 ALTER_COMPILE Procedure Parameters

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

Exceptions

Table 9-3 ALTER_COMPILE Procedure Exceptions

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.

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 9-4 ANALYZE_OBJECT Procedure Parameters

Parameter	Description
type	One of TABLE, CLUSTER or INDEX. If none of these, an ORA-20001 error is raised.
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 nonzero.
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 9-5 ANALYZE_OBJECT Procedure Exceptions

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.

IS_TRIGGER_FIRE_ONCE Function

This function returns TRUE if the specified DML or DDL trigger is set to fire once. Otherwise, it returns FALSE.

A fire once trigger fires in a user session but does not fire in the following cases:

• For changes made by a Streams apply process
• For changes made by executing one or more Streams apply errors using the EXECUTE_ERROR or EXECUTE_ALL_ERRORS procedure in the DBMS_APPLY_ADM package

  Note: Only DML and DDL triggers can be fire once. All other types of triggers always fire.

  See Also: "SET_TRIGGER_FIRING_PROPERTY Procedure"

Syntax

DBMS_DDL.IS_TRIGGER_FIRE_ONCE
   trig_owner         IN  VARCHAR2,
   trig_name          IN  VARCHAR2)
RETURN BOOLEAN;

Parameters

Table 9-6 IS_TRIGGER_FIRE_ONCE Function Parameters

Parameter	Description
trig_owner	Schema of trigger
trig_name	Name of trigger

SET_TRIGGER_FIRING_PROPERTY Procedure

This procedure sets the specified DML or DDL trigger's firing property. Use this procedure to control a DML or DDL trigger's firing property for changes:

• Applied by a Streams apply process
• Made by executing one or more Streams apply errors using the EXECUTE_ERROR or EXECUTE_ALL_ERRORS procedure in the DBMS_APPLY_ADM package.

You can specify one of the following settings for a trigger's firing property:

• If the fire_once parameter is set to TRUE for a trigger, then the trigger does not fire for these types of changes.
• If the fire_once parameter is set to FALSE for a trigger, then the trigger fires for these types of changes.

Regardless of the firing property set by this procedure, a trigger continues to fire when changes are made by means other than the apply process or apply error execution. For example, if a user session or an application makes a change, then the trigger continues to fire, regardless of the firing property.

Note: If you dequeue an error transaction from the error queue and execute it without using the DBMS_APPLY_ADM package, then relevant changes resulting from this execution cause a trigger to fire, regardless of the trigger firing property. Only DML and DDL triggers can be fire once. All other types of triggers always fire.

See Also: Oracle9i Streams for more information about the apply process and controlling a trigger's firing property

Syntax

DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY
   trig_owner         IN  VARCHAR2,
   trig_name          IN  VARCHAR2,
   fire_once          IN  BOOLEAN);

Parameters

Table 9-7 SET_TRIGGER_FIRING_PROPERTY Procedure Parameters

Parameter	Description
trig_owner	Schema of the trigger to set
trig_name	Name of the trigger to set
fire_once	If TRUE, then the trigger is set to fire once. By default, the fire_once parameter is set to TRUE for DML and DDL triggers. If FALSE, then the trigger is set to always fire.

ALTER_TABLE_REFERENCEABLE Procedure

This procedure reorganizes object tables and swizzles references. For example, assume you have an object table FOO and that references in other tables point to objects stored in FOO. If you want to change some of the table organization--for example, you want to make it an IOT or a partitioned table, or you want to reorganize the data more efficiently--you copy all data from FOO into FOO2. Then you use the alter_table_referenceable and alter_table_not_referenceable procedures to swizzle all existing references to point to FOO2 instead of FOO.

Syntax

DBMS_DDL.ALTER_TABLE_REFERENCEABLE
   TABLE_NAME       IN           VARCHAR2,
   TABLE_SCHEMA     IN  DEFAULT  VARCHAR2,
   AFFECTED_SCHEMA  IN  DEFAULT  VARCHAR2;

ALTER_TABLE_NOT_REFERENCEABLE Procedure

See ALTER_TABLE_NOT_REFERENCEABLE Procedure.

Syntax

DBMS_DDL.ALTER_TABLE_NOT_REFERENCEABLE
   TABLE_NAME       IN           VARCHAR2,
   TABLE_SCHEMA     IN  DEFAULT  VARCHAR2,
   AFFECTED_SCHEMA  IN  DEFAULT  VARCHAR2;