|Oracle9i Supplied PL/SQL Packages and Types Reference
Release 2 (9.2)
Part Number A96612-01
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.
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:
Oracle9i Application Developer's Guide - Fundamentals for information on how to create your own packages
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:
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.
Most Oracle supplied packages are automatically installed when the database is created and the
SQL script is run. For example, to create the
DBMS_ALERT package, the
PLB scripts must be run when connected as the user
SYS. These scripts are run automatically by the
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.
To create packages and store them permanently in an Oracle database, use the
BODY statements. You can execute these statements interactively from SQL*Plus or Enterprise Manager.
To create a new package, do the following:
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.
You can declare and define program objects in the package body.
for more information on creating new packages
for more information on storing and executing packages
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. Using this distinction, you can 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.
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;
If you want to try this example, then first create the sequence number
To reference the types, items, and subprograms declared in a package specification, use the dot notation. For example:
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
For example, if you want to use the
_ARG function to determine the value of a
ZONE argument in a deferred call, then you substitute
datatype. Therefore, you run the