CREATE PACKAGE

Syntax

invoker_rights_clause::=

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.

For information on creating standalone functions and procedures, see "CREATE FUNCTION" and "CREATE PROCEDURE". For information on modifying a package, see "ALTER PACKAGE". For information on dropping a package, see "DROP PACKAGE".

For detailed discussions of packages and how to use them, see Oracle8i Application Developer's Guide - Fundamentals and Oracle8i Supplied PL/SQL Packages Reference.

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

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.

See Also: PL/SQL User's Guide and Reference.

Keywords and Parameters

OR REPLACE	re-creates the package specification if it already exists. Use this clause to change the specification of an existing package without dropping, re-creating, and regranting object privileges previously granted on the package. If you change a package specification, Oracle recompiles it. For information on recompiling package specifications, see "ALTER PACKAGE".
	Users who had previously been granted privileges on a redefined package can still access the package without being regranted the privileges. If any function-based indexes depend on the package, Oracle marks the indexes DISABLED.
schema	is the schema to contain the package. If you omit schema, Oracle creates the package in your own schema.
package	is the name of the package to be created. If creating the package results in compilation errors, Oracle returns an error. You can see the associated compiler error messages with the SHOW ERRORS command.
invoker_rights_clause	lets you specify whether the functions and procedures in the package execute with the privileges and in the schema of the user who owns it or with the privileges and in the schema of CURRENT_USER. This specification applies to the corresponding package body as well. (For information on how CURRENT_USER is determined, see Oracle8i Concepts and Oracle8i Application Developer's Guide - Fundamentals.) This clause also determines how Oracle resolves external names in queries, DML operations, and dynamic SQL statements in the package. See Also: PL/SQL User's Guide and Reference.
	AUTHID CURRENT_USER	specifies that the package executes with the privileges of CURRENT_USER. This clause creates an "invoker-rights package." This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of CURRENT_USER. External names in all other statements resolve in the schema in which the package resides.
	AUTHID DEFINER	specifies that the package executes with the privileges of the owner of the schema in which the package resides and that external names resolve in the schema where the package resides. This is the default.
pl/sql_package_spec	is the package specification, which can contain type definitions, cursor declarations, variable declarations, constant declarations, exception declarations, PL/SQL subprogram specifications, and call specifications (declarations of a C or Java routine expressed in PL/SQL). For a list of restrictions on user-defined functions in a package, see "Restrictions on User-Defined Functions". See Also: PL/SQL User's Guide and Reference for more information on PL/SQL package program units. Oracle8i Supplied PL/SQL Packages Reference for information on Oracle supplied packages.

Example

The following 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 "CREATE PACKAGE BODY".