CREATE PACKAGE BODY

Purpose

Use the CREATE PACKAGE BODY statement to create the body of a stored package, which is an encapsulated collection of related procedures, stored functions, and other program objects stored together in the database. The body defines these objects.

Packages are an alternative to creating procedures and functions as standalone schema objects.

See Also: CREATE FUNCTION and CREATE PROCEDURE for information on creating standalone functions and procedures CREATE PACKAGE for a discussion of packages, including how to create packages "Examples" for some illustrations ALTER PACKAGE for information on modifying a package DROP PACKAGE for information on removing a package from the database

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

Syntax

Keywords and Parameters

OR REPLACE

Specify OR REPLACE to re-create the package body if it already exists. Use this clause to change the body of an existing package without dropping, re-creating, and regranting object privileges previously granted on it. If you change a package body, Oracle recompiles it.

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

See Also: ALTER PACKAGE for information on recompiling package bodies

schema

Specify the schema to contain the package. If you omit schema, Oracle creates the package in your current schema.

package

Specify the name of the package to be created.

pl/sql_package_body

Specify the package body, which can contain PL/SQL subprogram bodies or call specifications (declarations of a C or Java routine expressed in PL/SQL).

See Also: Oracle8i Application Developer's Guide - Fundamentals for more information on writing PL/SQL or C package program units Oracle8i Java Stored Procedures Developer's Guide for information on JAVA package program units "Restrictions on User-Defined Functions" for a list of restrictions on user-defined functions in a package

Examples

CREATE PACKAGE BODY Example

This SQL statement creates the body of the emp_mgmt package:

CREATE PACKAGE BODY emp_mgmt AS 
   tot_emps  NUMBER; 
   tot_depts NUMBER;

FUNCTION hire
   (ename VARCHAR2, 
    job VARCHAR2, 
    mgr NUMBER,
    sal NUMBER, 
    comm NUMBER, 
    deptno NUMBER)

RETURN NUMBER IS
   new_empno NUMBER(4);
BEGIN
   SELECT empseq.NEXTVAL
      INTO new_empno
      FROM DUAL;
   INSERT INTO emp
      VALUES (new_empno, ename, job, mgr, sal, comm, deptno, 
         tot_emps := tot_emps + 1;
   RETURN(new_empno);
END;

FUNCTION create_dept(dname VARCHAR2, loc VARCHAR2)
   RETURN NUMBER IS
      new_deptno NUMBER(4);
   BEGIN
      SELECT deptseq.NEXTVAL
         INTO new_deptno
         FROM dual;
      INSERT INTO dept
         VALUES (new_deptno, dname, loc);
            tot_depts := tot_depts + 1;
      RETURN(new_deptno);
   END;

PROCEDURE remove_emp(empno NUMBER) IS
   BEGIN
      DELETE FROM emp
      WHERE emp.empno = remove_emp.empno;
         tot_emps := tot_emps - 1;
   END; 

PROCEDURE remove_dept(deptno NUMBER) IS 
   BEGIN 
      DELETE FROM dept 
      WHERE dept.deptno = remove_dept.deptno; 
         tot_depts := tot_depts - 1; 
      SELECT COUNT(*) 
         INTO tot_emps 
         FROM emp; 
         /* In case Oracle deleted employees from the EMP table
         to enforce referential integrity constraints, reset
         the value of the variable TOT_EMPS to the total
         number of employees in the EMP table. */ 
   END; 

PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER) IS 
   curr_sal NUMBER(7,2); 
   BEGIN 
      SELECT sal 
      INTO curr_sal 
      FROM emp 
      WHERE emp.empno = increase_sal.empno; 
      IF curr_sal IS NULL 
         THEN RAISE no_sal; 
      ELSE 
         UPDATE emp 
         SET sal = sal + sal_incr 
         WHERE empno = empno; 
      END IF; 
   END; 

PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER) IS
      curr_comm NUMBER(7,2);
   BEGIN
      SELECT comm
      INTO curr_comm
      FROM emp
      WHERE emp.empno = increase_comm.empno
      IF curr_comm IS NULL
         THEN RAISE no_comm;
      ELSE 
         UPDATE emp
         SET comm = comm + comm_incr;
      END IF;
   END;

END emp_mgmt; 

This package body corresponds to the package specification in the example of the CREATE PACKAGE statement earlier in this chapter. The package body defines the public program objects declared in the package specification:

• The functions hire and create_dept

• The procedures remove_emp, remove_dept, increase_sal, and increase_comm

These objects are declared in the package specification, so they can be called by application programs, procedures, and functions outside the package. For example, if you have access to the package, you can create a procedure increase_all_comms separate from the emp_mgmt package that calls the increase_comm procedure.

These objects are defined in the package body, so you can change their definitions without causing Oracle to invalidate dependent schema objects. For example, if you subsequently change the definition of hire, Oracle need not recompile increase_all_comms before executing it.

The package body in this example also declares private program objects, the variables tot_emps and tot_depts. These objects are declared in the package body rather than the package specification, so they are accessible to other objects in the package, but they are not accessible outside the package. For example, you cannot develop an application that explicitly changes the value of the variable tot_depts. However, the function create_dept is part of the package, so create_dept can change the value of tot_depts.