Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 6.1 Package Components

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

6.1 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 contains implementation details and private declarations which are invisible to the application. You can debug, enhance or replace a package body without changing the specifications. Similarly, you can change the body without recompiling the calling programs because the implementation details are invisible to the application.

6.1.1 Package Specification Syntax

The following is the syntax of the package specification:

CREATE [ OR REPLACE ] PACKAGE package_name
  [ AUTHID { DEFINER | CURRENT_USER } ]
  { IS | AS }
  [ declaration; ] ...
  [ { PROCEDURE proc_name
      [ (parm1 [IN | IN OUT | OUT ] datatype1
      [, parm2 [IN | IN OUT | OUT ] datatype2 ] ...) ]; 

[PRAGMA RESTRICT_REFERENCES (proc_name, WNDS [, WNPS]

[, RNDS] [, RNPS] [, TRUST] )];

   |
      FUNCTION func_name
      [ (parm1 [IN | IN OUT | OUT ] datatype1
      [, parm2 [IN | IN OUT | OUT ] datatype2 ] ...) ] 
      RETURN return_type; } ...]

[PRAGMA RESTRICT_REFERENCES (func_name, WNDS [, WNPS]

[, RNDS] [, RNPS] [, TRUST] )];

END [ package_name ];

package_name is an identifier assigned to the package. If the AUTHID clause is omitted or DEFINER is specified, the rights and search path of the package owner are used to determine access privileges to database objects and resolve unqualified database object references, respectively. If CURRENT_USER is specified, the rights and search path of the current user executing a program in the package are used to determine access privileges and resolve unqualified object references. 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:

    ● Variable Declaration

    ● Record Declaration (see Section 4.3.4)

    ● Collection Declaration (see Section 4.10)

    REF CURSOR and Cursor Variable Declaration

    TYPE Definitions for Records, Collections, and REF CURSORs

    ● Object Variable Declaration (see Section 8.3)

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.

PRAGMA RESTRICT_REFERENCES is supported for syntax compatibility only; the pragma is parsed and ignored.

6.1.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:

    ● Variable Declaration

    ● Record Declaration (see Section 4.3.4)

    ● Collection Declaration (see Section 4.10)

    REF CURSOR and Cursor Variable Declaration

    TYPE Definitions for Records, Collections, and REF CURSORs

    ● Object Variable Declaration (see Section 8.3)

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.

Previous PageTable Of ContentsNext Page