Skip Headers

Oracle9i Supplied PL/SQL Packages and Types Reference
Release 2 (9.2)

Part Number A96612-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to beginning of chapter Go to next page

DBMS_DDL, 2 of 2


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:

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:

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

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;

Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 2000, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback