Oracle8i Supplied Packages Reference
Release 8.1.5

A68001-01

Library

Product

Contents

Index

Prev Next

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




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index