Oracle7 Server SQL Reference

CREATE PACKAGE

Purpose

To create the specification for a stored package. A package is an encapsulated collection of related procedures, functions, and other program objects stored together in the database. The specification declares these objects.

Prerequisites

Before a package can be created, the user SYS must run the SQL script DBMSSTDX.SQL . The exact name and location of this script may vary depending on your operating system.

To create a package in your own schema, you must have CREATE PROCEDURE system privilege. To create a package in another user's schema, you must have CREATE ANY PROCEDURE system privilege.

If you are using Trusted Oracle7 in DBMS MAC mode, you can only create a package in another user's schema if your DBMS label dominates the creation label of the other user.

To create a package, you must be using Oracle7 with PL/SQL installed. For more information, see PL/SQL User's Guide and Reference.

Syntax

Keywords and Parameters

OR REPLACE

recreates the package specification if it already exists. You can use this option to change the specification of an existing package without dropping, recreating, and regranting object privileges previously granted on the package. If you change a package specification, Oracle7 recompiles it. For information on recompiling package specifications, see the ALTER PROCEDURE command .

Users who had previously been granted privileges on a redefined package can still access the package without being regretted the privileges.

schema

is the schema to contain the package. If you omit schema, Oracle7 creates the package in your own schema.

package

is the name of the package to be created.

pl/sql_package_spec

is the package specification. The package specification can declare program objects. Package specifications are written in PL/SQL. For information on PL/SQL, including writing package specifications, see PL/SQL User's Guide and Reference.

To embed a CREATE PACKAGE statement inside an Oracle Precompiler program, you must terminate the statement with the keyword END-EXEC followed by the embedded SQL statement terminator for the specific language.

Packages

A package is an encapsulated collection of related program objects stored together in the database. Program objects are:

procedures

functions

variables

constants

cursors

exceptions

Using packages is an alternative to creating procedures and functions as stand-alone schema objects. Packages have many advantages over stand-alone procedures and functions:

Packages allow you to organize your application development more efficiently.

Packages allow you to grant privileges more efficiently.

Packages allow you to modify package objects without recompiling dependent schema objects.

Packages allow Oracle7 to read multiple package objects into memory at once.

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

Packages allow you to overload procedures or functions . Overloading a procedure means creating multiple procedures with the same name in the same package, each taking arguments of different number or datatype.

For more information on these and other benefits of packages, see the "Using Procedures and Packages" chapter of the Oracle7 Server Application Developer's Guide.

When you create a package in Trusted Oracle7, it is labeled with your DBMS label.

How to Create Packages

To create a package, you must perform two distinct steps:

1. Create the package specification with the CREATE PACKAGE command. 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.

2. Create the package body with the CREATE PACKAGE BODY command. You can declare and define program objects in the package body:

You must define public objects declared in the package specification.

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

See the CREATE PACKAGE BODY command 4 - 202.

The Separation of Specification and Body

Oracle7 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 Oracle7 to invalidate other schema objects that call or reference the program object. Oracle7 only invalidates dependent schema objects if you change the declaration of the program object in the package specification.

Example

This SQL statement creates the specification of the EMP_MGMT package:

CREATE PACKAGE emp_mgmt AS 
	FUNCTION hire(ename VARCHAR2, job VARCHAR2, mgr NUMBER,
				sal NUMBER, comm NUMBER, deptno NUMBER) 
		RETURN NUMBER; 
	FUNCTION create_dept(dname VARCHAR2, loc VARCHAR2) 
		RETURN NUMBER; 
	PROCEDURE remove_emp(empno NUMBER); 
	PROCEDURE remove_dept(deptno NUMBER); 
	PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER); 
	PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER); 
	no_comm EXCEPTION; 
	no_sal EXCEPTION; 
	END emp_mgmt

The specification for the EMP_MGMT package declares the following public program objects:

the functions HIRE and CREATE_DEPT

the procedures REMOVE_EMP, REMOVE_DEPT, INCREASE_SAL, and INCREASE_COMM

the exceptions NO_COMM and NO_SAL

All of these objects are available to users who have access to the package. After creating the package, you can develop applications that call any of the package's public procedures or functions or raise any of the package's public exceptions.

Before you can call this package's procedures and functions, you must define these procedures and functions in the package body. For an example of a CREATE PACKAGE BODY statement that creates the body of the EMP_MGMT package, see the CREATE PACKAGE BODY command .