16.3. Package Components

Packages consist of two main components:

The package specification: This is the public interface, (these are the elements which can be referenced outside the package). We declare all database objects that are to be a part of our package within the specification.
The package body: This contains the actual implementation of all the database objects declared within the package specification.

The package body implements the specifications in the package specification. It holds implementation details and private declarations which are invisible to the application. So we can debug, enhance or replace a package body without changing the specifications. Similarly we can change the body without recompiling the calling programs because the implementation details are invisible to the application.

16.3.1. Package Specification Syntax

The following is the syntax of the package specification:

CREATE [ OR REPLACE ] PACKAGE package_name
{ IS | AS }
   [ declaration; ] ...
[{PROCEDURE  proc_name [(
      parm1 [IN | IN OUT | OUT ] datatype1
   [, parm2 [IN | IN OUT | OUT ] datatype2 ]...)]; 
  |
   FUNCTION  func_name [ (
      parm1 [IN | IN OUT | OUT ] datatype1
   [, parm2 [IN | IN OUT | OUT ] datatype2 ]...)] 
   RETURN return_type ;} ... ]
END [ package_name ];

package_name is an identifier assigned to the package.

declaration is an identifier of a public variable. A public variable can be accessed from outside of the package using the syntax package_name.variable. There can be none, one, or more public variables. Public variable definitions must come before procedure or function declarations. declaration can be any of the following:

proc_name is an identifier of a public procedure. Public procedures can be invoked from outside of the package using the syntax package_name.proc_name[(...)]. If specified, parm1, parm2,... are the formal parameters of the procedure. datatype1, datatype2,... are the data types of parm1, parm2,... respectively. IN, IN OUT, and OUT are the possible parameter modes for each formal parameter. If none are specified, the default is IN.

func_name is an identifier of a public function. Public functions can be invoked from outside of the package using the syntax package_name.func_name[(...)]. If specified, parm1, parm2,... are the formal parameters of the function. datatype1, datatype2 ,... are the data types of parm1, parm2, ... respectively. IN, IN OUT, and OUT are the possible parameter modes for each formal parameter. If none are specified, the default is IN. return_type is the data type of the value the function returns. IN parameters can also be initialized with a default value which is used in place of any IN parameter you miss.

16.3.2. Package Body Syntax

The following is the syntax for the package body:

CREATE [ OR REPLACE ] PACKAGE BODY package_name
{ IS | AS }
   [ private_declaration; ] ...
[{PROCEDURE proc_name [(
      parm1 [IN | IN OUT | OUT ] datatype1
   [, parm2 [IN | IN OUT | OUT] datatype2 ]...)]
{ IS | AS }
      [ proc_declaration; ]...
   BEGIN
       statement;...
   [EXCEPTION
        WHEN ... THEN
            statement;... ]
       
   END;
  |
   FUNCTION func_name [(
        parm1 [IN | IN OUT | OUT ] datatype1
     [, parm2 [IN | IN OUT | OUT ] datatype2 ]...)] 
      RETURN return_type   
   {IS | AS }
      [ func_declaration; ]... 
   BEGIN
       statement;...  
   [EXCEPTION
        WHEN ... THEN
            statement;... ]
       
   END; } ... ]
  [BEGIN
      init_statement;...]     
END [ package_name ];

package_name is the name of the package for which this is the package body. There must be an existing package specification with the same name.

private_declaration is an identifier of a private variable that can be accessed by any procedure or function within the package. There can be none, one, or more private variables. private_declaration can be any of the following:

If proc_name is the same as the identifier of a public procedure declared in the package specification and the signature of proc_name (i.e., formal parameter names (parm1, parm2,...), data types (datatype1, datatype2,...), parameter modes, order of formal parameters, and number of formal parameters) exactly matches the signature of the public procedure's declaration, then proc_name defines the body of this public procedure.

If the conditions described in the prior paragraph are not true, then proc_name defines a private procedure.

parm1, parm2,... are the formal parameters of the procedure. datatype1, datatype2,... are the data types of parm1, parm2,... respectively. IN, IN OUT, and OUT are the possible parameter modes for each formal parameter. If none are specified, the default is IN. IN parameters can also be initialized with a default value which is used in place of any IN parameter you miss.

proc_variable is an identifier of a variable that can be accessed only from within procedure, proc_name. There can be none, one, or more variables. datatype is the data type of proc_variable. statement is an SPL program statement.

If func_name is the same as the identifier of a public function declared in the package specification and the signature of func_name (i.e., formal parameter names (parm1, parm2,...), data types (datatype1, datatype2,...), parameter modes, order of formal parameters, and number of formal parameters) exactly matches the signature of the public function's declaration, then func_name defines the body of this public function.

If the conditions described in the prior paragraph are not true, then func_name defines a private function.

parm1, parm2,... are the formal parameters of the function. datatype1, datatype2,... are the data types of parm1, parm2,... respectively. IN, IN OUT, and OUT are the possible parameter modes for each formal parameter. If none are specified, the default is IN. return_type is the data type of the value returned by the function.

func_variable is an identifier of a variable that can be accessed only from within function, func_name. There can be none, one, or more variables. datatype is the data type of func_variable. statement is an SPL program statement.

init_statement is a statement in the initialization section of the package body. The initialization section, if specified, must contain at least one statement. The statements in the initialization section are executed once per user's session when the package is first referenced in that session.