1
Introduction

Oracle supplies many PL/SQL packages with the Oracle server to extend database functionality and provide PL/SQL access to SQL features. You can use the supplied packages when creating your applications or for ideas in creating your own stored procedures.

Note: This manual covers the packages provided with the Oracle database server. Packages supplied with other products, such as Oracle Developer or the Oracle Application Server, are not covered.

This chapter contains the following topics:

• Package Overview

• Summary of Oracle Supplied PL/SQL Packages

• Summary of Subprograms in Supplemental Packages

  See Also: For information on how to create your own packages, see the Oracle9i Application Developer's Guide - Fundamentals.

Package Overview

A package is an encapsulated collection of related program objects stored together in the database. Program objects are procedures, functions, variables, constants, cursors, and exceptions.

Packages have many advantages over standalone procedures and functions. For example, they:

• Let you organize your application development more efficiently.

• Let you grant privileges more efficiently.

• Let you modify package objects without recompiling dependent schema objects.

• Enable Oracle to read multiple package objects into memory at once.

• Let you overload procedures or functions. Overloading means creating multiple procedures with the same name in the same package, each taking arguments of different number or datatype.

• Can contain global variables and cursors that are available to all procedures and functions in the package.

Package Components

PL/SQL packages have two parts: the specification and the body, although sometimes the body is unnecessary. The specification is the interface to your application; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the specification.

Unlike subprograms, packages cannot be called, parameterized, or nested. However, the formats of a package and a subprogram are similar:

CREATE PACKAGE name AS  -- specification (visible part)
   -- public type and item declarations
   -- subprogram specifications
END [name];

CREATE PACKAGE BODY name AS  -- body (hidden part)
   -- private type and item declarations
   -- subprogram bodies
[BEGIN
   -- initialization statements]
END [name];

The specification holds public declarations that are visible to your application. The body holds implementation details and private declarations that are hidden from your application. You can debug, enhance, or replace a package body without changing the specification. You can change a package body without recompiling calling programs because the implementation details in the body are hidden from your application.

Using Oracle Supplied Packages

Most Oracle supplied packages are automatically installed when the database is created and the CATPROC.SQL script is run. For example, to create the DBMS_ALERT package, the DBMSALRT.SQL and PRVTALRT.PLB scripts must be run when connected as the user SYS. These scripts are run automatically by the CATPROC.SQL script.

Certain packages are not installed automatically. Special installation instructions for these packages are documented in the individual chapters.

To call a PL/SQL function from SQL, you must either own the function or have EXECUTE privileges on the function. To select from a view defined with a PL/SQL function, you must have SELECT privileges on the view. No separate EXECUTE privileges are needed to select from the view. Instructions on special requirements for packages are documented in the individual chapters.

Creating New Packages

To create packages and store them permanently in an Oracle database, use the CREATE PACKAGE and CREATE PACKAGE BODY statements. You can execute these statements interactively from SQL*Plus or Enterprise Manager.

To create a new package, do the following:

1. Create the package specification with the CREATE PACKAGE statement.

   You can declare program objects in the package specification. Such objects are called public objects. Public objects can be referenced outside the package, as well as by other objects in the package.

   Note: It is often more convenient to add the OR REPLACE clause in the CREATE PACKAGE statement.

2. Create the package body with the CREATE PACKAGE BODY statement.

   You can declare and define program objects in the package body.

   • You must define public objects declared in the package specification.

   • You can declare and define additional package objects, called private objects. Private objects are declared in the package body rather than in the package specification, so they can be referenced only by other objects in the package. They cannot be referenced outside the package.

     See Also: For more information on creating new packages, see PL/SQL User's Guide and Reference and Oracle9i Application Developer's Guide - Fundamentals. For more information on storing and executing packages, see Oracle9i Database Concepts.

Separating the Specification and Body

The specification of a package declares the public types, variables, constants, and subprograms that are visible outside the immediate scope of the package. The body of a package defines the objects declared in the specification, as well as private objects that are not visible to applications outside the package.

Oracle stores the specification and body of a package separately in the database. Other schema objects that call or reference public program objects depend only on the package specification, not on the package body. This distinction allows you to change the definition of a program object in the package body without causing Oracle to invalidate other schema objects that call or reference the program object. Oracle invalidates dependent schema objects only if you change the declaration of the program object in the package specification.

Example

The following example shows a package specification for a package named EMPLOYEE_MANAGEMENT. The package contains one stored function and two stored procedures.

CREATE PACKAGE employee_management AS
   FUNCTION hire_emp (name VARCHAR2, job VARCHAR2,
      mgr NUMBER, hiredate DATE, sal NUMBER, comm NUMBER,
      deptno NUMBER) RETURN NUMBER;
   PROCEDURE fire_emp (emp_id NUMBER);
   PROCEDURE sal_raise (emp_id NUMBER, sal_incr NUMBER);
END employee_management;

The body for this package defines the function and the procedures:

CREATE PACKAGE BODY employee_management AS
   FUNCTION hire_emp (name VARCHAR2, job VARCHAR2,
      mgr NUMBER, hiredate DATE, sal NUMBER, comm NUMBER,
      deptno NUMBER) RETURN NUMBER IS

The function accepts all arguments for the fields in the employee table except for the employee number. A value for this field is supplied by a sequence. The function returns the sequence number generated by the call to this function.

       new_empno    NUMBER(10);

   BEGIN
      SELECT emp_sequence.NEXTVAL INTO new_empno FROM dual;
      INSERT INTO emp VALUES (new_empno, name, job, mgr,
         hiredate, sal, comm, deptno);
      RETURN (new_empno);
   END hire_emp;

   PROCEDURE fire_emp(emp_id IN NUMBER) AS

The procedure deletes the employee with an employee number that corresponds to the argument emp_id. If no employee is found, then an exception is raised.

   BEGIN
      DELETE FROM emp WHERE empno = emp_id;
      IF SQL%NOTFOUND THEN
      raise_application_error(-20011, 'Invalid Employee
         Number: ' || TO_CHAR(emp_id));
   END IF;
END fire_emp;

PROCEDURE sal_raise (emp_id IN NUMBER, sal_incr IN NUMBER) AS

The procedure accepts two arguments. Emp_id is a number that corresponds to an employee number. Sal_incr is the amount by which to increase the employee's salary.

   BEGIN

   -- If employee exists, then update salary with increase.
   
      UPDATE emp
         SET sal = sal + sal_incr
         WHERE empno = emp_id;
      IF SQL%NOTFOUND THEN
         raise_application_error(-20011, 'Invalid Employee
            Number: ' || TO_CHAR(emp_id));
      END IF;
   END sal_raise;
END employee_management;

Note: If you want to try this example, then first create the sequence number emp_sequence. You can do this using the following SQL*Plus statement: SQL> CREATE SEQUENCE emp_sequence > START WITH 8000 INCREMENT BY 10;

Referencing Package Contents

To reference the types, items, and subprograms declared in a package specification, use the dot notation. For example:

package_name.type_name
package_name.item_name
package_name.subprogram_name

Abbreviations for Datetime and Interval Datatypes

Many of the datetime and interval datatypes have names that are too long to be used with the procedures and functions in the replication management API. Therefore, you must use abbreviations for these datatypes instead of the full names. The following table lists each datatype and its abbreviation. No abbreviation is necessary for the DATE and TIMESTAMP datatypes.

Datatype	Abbreviation
TIMESTAMP WITH TIME ZONE	TSTZ
TIMESTAMP LOCAL TIME ZONE	TSLTZ
INTERVAL YEAR TO MONTH	IYM
INTERVAL DAY TO SECOND	IDS

For example, if you want to use the DBMS_DEFER_QUERY.GET_datatype_ARG function to determine the value of a TIMESTAMP LOCAL TIME ZONE argument in a deferred call, then you substitute TSLTZ for datatype. Therefore, you run the DBMS_DEFER_QUERY.GET_TSLTZ_ARG function.

Summary of Oracle Supplied PL/SQL Packages

Table 1-1 lists the supplied PL/SQL server packages. These packages run as the invoking user, rather than the package owner. Unless otherwise noted, the packages are callable through public synonyms of the same name.

Caution: The procedures and functions provided in these packages and their external interfaces are reserved by Oracle and are subject to change. Modifying Oracle supplied packages can cause internal errors and database security violations. Do not modify supplied packages.

Table 1-1 Summary of Oracle Supplied PL/SQL Packages  

Package Name	Description	Documentation
DBMS_ALERT	Provides support for the asynchronous notification of database events.	Chapter 2
DBMS_APPLICATION_INFO	Lets you register an application name with the database for auditing or performance tracking purposes.	Chapter 3
DBMS_AQ	Lets you add a message (of a predefined object type) onto a queue or to dequeue a message.	Chapter 4
DBMS_AQADM	Lets you perform administrative functions on a queue or queue table for messages of a predefined object type.	Chapter 5
DBMS_AQELM	Provides procedures to manage the configuration of Advanced Queuing asynchronous notification by e-mail and HTTP.	Chapter 6
DBMS_BACKUP_RESTORE	Normalizes filenames on Windows NT platforms.	Chapter 7
DBMS_DDL	Provides access to some SQL DDL statements from stored procedures, and provides special administration operations not available as DDLs.	Chapter 8
DBMS_DEBUG	Implements server-side debuggers and provides a way to debug server-side PL/SQL program units.	Chapter 9
DBMS_DEFER	Provides the user interface to a replicated transactional deferred remote procedure call facility. Requires the Distributed Option.	Chapter 10
DBMS_DEFER_QUERY	Permits querying the deferred remote procedure calls (RPC) queue data that is not exposed through views. Requires the Distributed Option.	Chapter 11
DMBS_DEFER_SYS	Provides the system administrator interface to a replicated transactional deferred remote procedure call facility. Requires the Distributed Option.	Chapter 12
DBMS_DESCRIBE	Describes the arguments of a stored procedure with full name translation and security checking.	Chapter 13
DBMS_DISTRIBUTED_TRUST_ADMIN	Maintains the Trusted Database List, which is used to determine if a privileged database link from a particular server can be accepted.	Chapter 14
DBMS_FGA	Provides fine-grained security functions.	Chapter 15
DMBS_FLASHBACK	Lets you flash back to a version of the database at a specified wall-clock time or a specified system change number (SCN).	Chapter 16
DBMS_HS_PASSTHROUGH	Lets you use Heterogeneous Services to send pass-through SQL statements to non-Oracle systems.	Chapter 17
DBMS_IOT	Creates a table into which references to the chained rows for an Index Organized Table can be placed using the ANALYZE command.	Chapter 18
DBMS_JOB	Lets you schedule administrative procedures that you want performed at periodic intervals; it is also the interface for the job queue.	Chapter 19
DBMS_LDAP	Provides functions and procedures to access data from LDAP servers.	Chapter 20
DBMS_LIBCACHE	Prepares the library cache on an Oracle instance by extracting SQL and PL/SQL from a remote instance and compiling this SQL locally without execution.	Chapter 21
DBMS_LOB	Provides general purpose routines for operations on Oracle Large Object (LOBs) datatypes - BLOB, CLOB (read-write), and BFILEs (read-only).	Chapter 22
DBMS_LOCK	Lets you request, convert and release locks through Oracle Lock Management services.	Chapter 23
DBMS_LOGMNR	Provides functions to initialize and run the log reader.	Chapter 24
DBMS_LOGMNR_CDC_PUBLISH	Identifies new data that has been added to, modified, or removed from, relational tables and publishes the changed data in a form that is usable by an application.	Chapter 25
DBMS_LOGMNR_CDC_SUBSCRIBE	Lets you view and query the change data that was captured and published with the DBMS_LOGMNR_CDC_PUBLISH package.	Chapter 26
DBMS_LOGMNR_D	Queries the dictionary tables of the current database, and creates a text based file containing their contents.	Chapter 27
DBMS_METADATA	Lets callers easily retrieve complete database object definitions (metadata) from the dictionary.	Chapter 28
DBMS_MVIEW	Lets you refresh snapshots that are not part of the same refresh group and purge logs. DBMS_SNAPSHOT is a synonym.	Chapter 29
DBMS_OBFUSCATION_TOOLKIT	Provides procedures for Data Encryption Standards.	Chapter 30
DBMS_ODCI	Returns the CPU cost of a user function based on the elapsed time of the function.	Chapter 31
DBMS_OFFLINE_OG	Provides public APIs for offline instantiation of master groups.	Chapter 32
DBMS_OFFLINE_SNAPSHOT	Provides public APIs for offline instantiation of snapshots.	Chapter 33
DBMS_OLAP	Provides procedures for summaries, dimensions, and query rewrites.	Chapter 34
DBMS_ORACLE_TRACE_AGENT	Provides client callable interfaces to the Oracle TRACE instrumentation within the Oracle7 Server.	Chapter 35
DBMS_ORACLE_TRACE_USER	Provides public access to the Oracle release 7 Server Oracle TRACE instrumentation for the calling user.	Chapter 36
DBMS_OUTLN	Provides the interface for procedures and functions associated with management of stored outlines. Synonymous with OUTLN_PKG	Chapter 37
DBMS_OUTLN_EDIT	Lets you edit an invoker's rights package.	Chapter 38
DBMS_OUTPUT	Accumulates information in a buffer so that it can be retrieved out later.	Chapter 39
DBMS_PCLXUTIL	Provides intra-partition parallelism for creating partition-wise local indexes.	Chapter 40
DBMS_PIPE	Provides a DBMS pipe service which enables messages to be sent between sessions.	Chapter 41
DBMS_PROFILER	Provides a Probe Profiler API to profile existing PL/SQL applications and identify performance bottlenecks.	Chapter 42
DBMS_RANDOM	Provides a built-in random number generator.	Chapter 43
DBMS_RECTIFIER_DIFF	Provides APIs used to detect and resolve data inconsistencies between two replicated sites.	Chapter 44
DBMS_REDEFINITION	Lets you perform an online reorganization of tables.	Chapter 45
DBMS_REFRESH	Lets you create groups of snapshots that can be refreshed together to a transactionally consistent point in time. Requires the Distributed Option.	Chapter 46
DBMS_REPAIR	Provides data corruption repair procedures.	Chapter 47
DBMS_REPCAT	Provides routines to administer and update the replication catalog and environment. Requires the Replication Option.	Chapter 48
DBMS_REPCAT_ADMIN	Lets you create users with the privileges needed by the symmetric replication facility. Requires the Replication Option.	Chapter 49
DBMS_REPCAT_INSTATIATE	Instantiates deployment templates. Requires the Replication Option.	Chapter 50
DBMS_REPCAT_RGT	Controls the maintenance and definition of refresh group templates. Requires the Replication Option.	Chapter 51
DBMS_REPUTIL	Provides routines to generate shadow tables, triggers, and packages for table replication.	Chapter 52
DBMS_RESOURCE_MANAGER	Maintains plans, consumer groups, and plan directives; it also provides semantics so that you may group together changes to the plan schema.	Chapter 53
DBMS_RESOURCE_MANAGER_PRIVS	Maintains privileges associated with resource consumer groups.	Chapter 54
DBMS_RESUMABLE	Lets you suspend large operations that run out of space or reach space limits after executing for a long time, fix the problem, and make the statement resume execution.	Chapter 55
DBMS_RLS	Provides row level security administrative interface.	Chapter 56
DBMS_ROWID	Provides procedures to create rowids and to interpret their contents.	Chapter 57
DBMS_SESSION	Provides access to SQL ALTER SESSION statements, and other session information, from stored procedures.	Chapter 58
DBMS_SHARED_POOL	Lets you keep objects in shared memory, so that they will not be aged out with the normal LRU mechanism.	Chapter 59
DBMS_SNAPSHOT	Synonym for DBMS_MVIEW	Chapter 29
DBMS_SPACE	Provides segment space information not available through standard SQL.	Chapter 60
DBMS_SPACE_ADMIN	Provides tablespace and segment space administration not available through the standard SQL.	Chapter 61
DBMS_SQL	Lets you use dynamic SQL to access the database.	Chapter 62
DBMS_STANDARD	Provides language facilities that help your application interact with Oracle.	Refer to Note #1
DBMS_STATS	Provides a mechanism for users to view and modify optimizer statistics gathered for database objects.	Chapter 63
DBMS_TRACE	Provides routines to start and stop PL/SQL tracing.	Chapter 64
DBMS_TRANSACTION	Provides access to SQL transaction statements from stored procedures and monitors transaction activities.	Chapter 65
DBMS_TRANSFORM	Provides an interface to the message format transformation features of Oracle Advanced Queuing.	Chapter 66
DBMS_TTS	Checks if the transportable set is self-contained.	Chapter 67
DBMS_TYPES	Consists of constants, which represent the built-in and user-defined types.	Chapter 68
DBMS_UTILITY	Provides various utility routines.	Chapter 69
DBMS_WM	Describes how to use the the programming interface to Oracle Database Workspace Manager to work with long transactions.	Chapter 70
DBMS_XMLGEN	Converts the results of a SQL query to a canonical XML format.	Chapter 71
DMBS_XMLQUERY	Provides database-to-XMLType functionality.	Chapter 72
DBMS_XMLSAVE	Provides XML-to-database-type functionality.	Chapter 73
DEBUG_EXTPROC	Lets you debug external procedures on platforms with debuggers that can attach to a running process.	Chapter 74
OUTLN_PKG	Synonym of DBMS_OUTLN.	Chapter 37
PLITBLM	Handles index-table operations.	Refer to Note #1
SDO_CS (refer to Note #2)	Provides functions for coordinate system transformation.	Oracle Spatial User's Guide and Reference
SDO_GEOM (refer to Note #2)	Provides functions implementing geometric operations on spatial objects.	Oracle Spatial User's Guide and Reference
SDO_LRS (refer to Note #2)	Provides functions for linear referencing system support.	Oracle Spatial User's Guide and Reference
SDO_MIGRATE (refer to Note #2)	Provides functions for migrating spatial data from previous releases.	Oracle Spatial User's Guide and Reference
SDO_TUNE (refer to Note #2)	Provides functions for selecting parameters that determine the behavior of the spatial indexing scheme used in Oracle Spatial.	Oracle Spatial User's Guide and Reference
STANDARD	Declares types, exceptions, and subprograms which are available automatically to every PL/SQL program.	Refer to Note #1
UTL_COLL	Enables PL/SQL programs to use collection locators to query and update.	Chapter 75
UTL_ENCODE	Provides functions that encode RAW data into a standard encoded format so that the data can be transported between hosts.	Chapter 76
UTL_FILE	Enables your PL/SQL programs to read and write operating system text files and provides a restricted version of standard operating system stream file I/O.	Chapter 77
UTL_HTTP	Enables HTTP callouts from PL/SQL and SQL to access data on the Internet or to call Oracle Web Server Cartridges.	Chapter 78
UTL_INADDR	Provides a procedure to support internet addressing.	Chapter 79
UTL_PG	Provides functions for converting COBOL numeric data into Oracle numbers and Oracle numbers into COBOL numeric data.	Oracle Procedural Gateway for APPC User's Guide
UTL_RAW	Provides SQL functions for RAW datatypes that concat, substr, etc. to and from RAWS.	Chapter 80
UTL_REF	Enables a PL/SQL program to access an object by providing a reference to the object.	Chapter 81
UTL_SMTP	Provides PL/SQL functionality to send emails.	Chapter 82
UTL_TCP	Provides PL/SQL functionality to support simple TCP/IP-based communications between servers and the outside world.	Chapter 83
UTL_URL	Provides escape and unescape mechanisms for URL characters.	Chapter 84
ANYDATA TYPE	A self-describing data instance type containing an instance of the type plus a description	Chapter 85
ANYDATASET TYPE	Contains a description of a given type plus a set of data instances of that type	Chapter 86
ANYTYPE TYPE	Contains a type description of any persistent SQL type, named or unnamed, including object types and collection types; or, it can be used to construct new transient type descriptions	Chapter 87
Note #1 The DBMS_STANDARD, STANDARD, and PLITBLM packages contain subprograms to help implement basic language features. Oracle does not recommend that the subprograms be directly called. For this reason, these three supplied packages are not documented in this book.
Note #2 Spatial packages are installed in user MDSYS with public synonyms.

Summary of Subprograms in Supplemental Packages

The packages listed in this section are documented in other Oracle books. See Table 1-1 for the documentation reference for each package. See Table 1-2 through Table 1-7 for the subprograms provided with these packages.