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

This chapter contains the following topics:

• Using DBMS_DDL
  • Security Model
  • Operational Notes
• Summary of DBMS_DDL Subprograms

---

Using DBMS_DDL

---

Security Model

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

---

Operational Notes

The ALTER_COMPILE procedure commits the current transaction, performs the operation, and then commits again.

---

Summary of DBMS_DDL Subprograms

Table 26-1 DBMS_DDL Package Subprograms

Subprogram	Description
ALTER_COMPILE Procedure	Compiles the PL/SQL object
ALTER_TABLE_NOT_REFERENCEABLE Procedure	Reorganizes object tables and swizzles references
ALTER_TABLE_REFERENCEABLE Procedure	Reorganizes object tables and swizzles references
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_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 26-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 26-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.

---

ALTER_TABLE_NOT_REFERENCEABLE Procedure

This procedure alters the given object table table_schema.table_name so it becomes not the default referenceable table for the schema affected_schema. This is equivalent to SQL

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

which is currently not supported or available as a DDL statement.

Syntax

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

Parameters

Table 26-4 ALTER_TABLE_NOT_REFERENCEABLE Procedure Parameters

Parameter	Description
table_name	The name of the table to be altered. Cannot be a synonym. Must not be NULL. Case sensitive.
table_schema	The name of the schema owning the table to be altered. If NULL then the current schema is used. Case sensitive.
affected_schema	The name of the schema affected by this alteration. If NULL then the current schema is used. Case sensitive.

Usage Notes

This procedure simply reverts for the affected schema to the default table referenceable for PUBLIC; that is., it simply undoes the previous ALTER_TABLE_REFERENCEABLE call for this specific schema. The affected schema must a particular schema (cannot be PUBLIC).

The user that executes this procedure must own the table (that is, the schema is the same as the user), and the affected schema must be the same as the user.

If the user executing this procedure has ALTER ANY TABLE and SELECT ANY TABLE and DROP ANY TABLE privileges, the user doesn't have to own the table and the affected schema can be any valid schema.

---

ALTER_TABLE_REFERENCEABLE Procedure

This procedure alters the given object table table_schema.table_name so it becomes the referenceable table for the given schema affected_schema. This is equivalent to SQL

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

which is currently not supported or available as a DDL statement.

Syntax

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

Parameters

Table 26-5 ALTER_TABLE_REFERENCEABLE Procedure Parameters

Parameter	Description
table_name	The name of the table to be altered. Cannot be a synonym. Must not be NULL. Case sensitive.
table_schema	The name of the schema owning the table to be altered. If NULL then the current schema is used. Case sensitive.
affected_schema	The name of the schema affected by this alteration. If NULL then the current schema is used. Case sensitive.

Usage Notes

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 makes it possible for you to create an object table and to assign to the new table the same 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 table with the same EOID; the new table is not referenceable, the original one is. All references that used to point to the objects in the original table still reference the same objects in the same original table.

If you execute this procedure on the new table, it will make the new table the referenceable table replacing the original one; thus, those references now point to the objects in the new table instead of the original table.

---

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

Syntax

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

Parameters

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

Usage Notes

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.