Package components v17

Suggest edits

Packages consist of two main components:

  • The package specification, which is the public interface. You can reference these elements outside the package. Declare all database objects that are a part of a package in the specification.

  • The package body, which contains the actual implementation of all the database objects declared in the package specification.

    The package body implements the specifications in the package specification. It contains implementation details and private declarations that 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.

Package specification syntax

The package specification defines the user interface for a package (the API). The specification lists the functions, procedures, types, exceptions, and cursors that are visible to a user of the package.

The syntax used to define the interface for a package is:

CREATE [ OR REPLACE ] PACKAGE <package_name>
  [ <authorization_clause> ]
  { IS | AS }
  [ <declaration>; ] ...
  [ <procedure_or_function_declaration> ] ...
END [ <package_name> ] ;

Where authorization_clause :=

{ AUTHID DEFINER } | { AUTHID CURRENT_USER }

Where procedure_or_function_declaration :=

procedure_declaration | function_declaration

Where procedure_declaration :=

PROCEDURE proc_name [ argument_list ];
[ restriction_pragma; ]

Where function_declaration :=

FUNCTION func_name [ argument_list ]
  RETURN rettype [ DETERMINISTIC ];
[ restriction_pragma; ]

Where argument_list :=

( argument_declaration [, ...] )

Where argument_declaration :=

argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ]

Where restriction_pragma :=

PRAGMA RESTRICT_REFERENCES(name, restrictions)

Where restrictions :=

restriction [, ... ]

Parameters

package_name

package_name is an identifier assigned to the package. Each package must have a unique name in the schema.

AUTHID DEFINER

If you omit the AUTHID clause or specify AUTHID DEFINER, the privileges of the package owner are used to determine access privileges to database objects.

AUTHID CURRENT_USER

If you specify AUTHID CURRENT_USER, the privileges of the current user executing a program in the package are used to determine access privileges.

declaration

declaration is an identifier of a public variable. You can access a public variable from outside the package using the syntax package_name.variable. There can be zero, 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
  • Collection declaration
  • REF CURSOR and cursor variable declaration
  • TYPE definitions for records, ollections, and REF CURSOR
  • Exception
  • Object variable declaration

proc_name

The name of a public procedure.

argname

The name of an argument. The argument is referenced by this name in the function or procedure body.

IN | IN OUT | OUT

The argument mode. IN (the default) declares the argument for input only. IN OUT allows the argument to receive a value as well as return a value. OUT specifies the argument is for output only.

argtype

The data types of an argument. An argument type can be a base data type, a copy of the type of an existing column using %TYPE, or a user-defined type such as a nested table or an object type. Don't specify a length for any base type. For example, specify VARCHAR2, not VARCHAR2(10).

Reference the type of a column by writing tablename.columnname %TYPE. Using this nomenclature can sometimes help make a procedure independent from changes to the definition of a table.

DEFAULT value

The DEFAULT clause supplies a default value for an input argument if you don't supply one in the invocation. You can't specify DEFAULT for arguments with modes IN OUT or OUT.

func_name

The name of a public function.

rettype

The return data type.

DETERMINISTIC

DETERMINISTIC is a synonym for IMMUTABLE. A DETERMINISTIC function can't modify the database and always reaches the same result when given the same argument values. It doesn't do database lookups or otherwise use information not directly present in its argument list. If you include this clause, any call of the function with all-constant arguments can be immediately replaced with the function value.

restriction

The following keywords are accepted for compatibility and ignored:

RNDS

RNPS

TRUST

WNDS

WNPS

Package body syntax

Package implementation details reside in the package body. The package body can contain objects that aren't visible to the package user. EDB Postgres Advanced Server supports the following syntax for the package body:

CREATE [ OR REPLACE ] PACKAGE BODY <package_name>
  { IS | AS }
  [ <private_declaration>; ] ...
  [ <procedure_or_function_definition> ] ...
  [ <package_initializer> ]
END [ <package_name> ] ;

Where procedure_or_function_definition :=

procedure_definition | function_definition

Where procedure_definition :=

PROCEDURE proc_name[ argument_list ]
  [ options_list ]
  { IS | AS }
    procedure_body
  END [ proc_name ] ;

Where procedure_body :=

[ PRAGMA AUTONOMOUS_TRANSACTION; ]
[ declaration; ] [, ...]
BEGIN
  statement; [...]
[ EXCEPTION
   { WHEN exception [OR exception] [...]] THEN statement; }
   [...]
]

Where function_definition :=

FUNCTION func_name [ argument_list ]
  RETURN rettype [ DETERMINISTIC ]
  [ options_list ]
  { IS | AS }
    function_body
  END [ func_name ] ;

Where function_body :=

[ PRAGMA AUTONOMOUS_TRANSACTION; ]
[ declaration; ] [, ...]
BEGIN
  statement; [...]
[ EXCEPTION
  { WHEN exception [ OR exception ] [...] THEN statement; }
  [...]
]

Where argument_list :=

( argument_declaration [, ...] )

Where argument_declaration :=

argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ]

Where options_list :=

option [ ... ]

Where option :=

STRICT
LEAKPROOF
PARALLEL { UNSAFE | RESTRICTED | SAFE }
COST execution_cost
ROWS result_rows
SET config_param { TO value | = value | FROM CURRENT }

Where package_initializer :=

BEGIN
 statement; [...]
END;

Parameters

package_name

package_name is the name of the package for which this is the package body. An package specification with this name must already exist.

private_declaration

private_declaration is an identifier of a private variable that any procedure or function can access in the package. There can be zero, one, or more private variables. private_declaration can be any of the following:

  • Variable declaration
  • Record declaration
  • Collection declaration
  • REF CURSOR and cursor variable declaration
  • TYPE definitions for records, collections, and REF CURSORs
  • Exception
  • Object variable declaration

proc_name

The name of the procedure being created.

PRAGMA AUTONOMOUS_TRANSACTION

PRAGMA AUTONOMOUS_TRANSACTION is the directive that sets the procedure as an autonomous transaction.

declaration

A variable, type, REF CURSOR, or subprogram declaration. If you include subprogram declarations, declare them after all other variable, type, and REF CURSOR declarations.

statement

An SPL program statement. A DECLARE - BEGIN - END block is considered an SPL statement unto itself. Thus, the function body can contain nested blocks.

exception

An exception condition name such as NO_DATA_FOUND, OTHERS.

func_name

The name of the function being created.

rettype

The return data type, which can be any of the types listed for argtype. As for argtype, don't specify a length for rettype.

DETERMINISTIC

Include DETERMINISTIC to specify for the function to always return the same result when given the same argument values. A DETERMINISTIC function must not modify the database.

!!! Note The DETERMINISTIC keyword is equivalent to the PostgreSQL IMMUTABLE option.

!!!Note If DETERMINISTIC is specified for a public function in the package body, you must also specify it for the function declaration in the package specification. For private functions, there's no function declaration in the package specification.

PRAGMA AUTONOMOUS_TRANSACTION

PRAGMA AUTONOMOUS_TRANSACTION is the directive that sets the function as an autonomous transaction.

argname

The name of a formal argument. The argument is referenced by this name in the procedure body.

IN | IN OUT | OUT

The argument mode. IN (the default) declares the argument for input only. IN OUT allows the argument to receive a value as well as return a value. OUT specifies the argument is for output only.

argtype

The data types of an argument. An argument type can be a base data type, a copy of the type of an existing column using %TYPE, or a user-defined type such as a nested table or an object type. Don't specify a length for any base type. For example, specify VARCHAR2, not VARCHAR2(10).

Reference the type of a column by writing tablename.columnname%TYPE. Using this nomenclature can sometimes help make a procedure independent from changes to the definition of a table.

DEFAULT value

The DEFAULT clause supplies a default value for an input argument if you don't supply one in the procedure call. Don't specify DEFAULT for arguments with modes IN OUT or OUT.

The following options aren't compatible with Oracle databases. They're extensions to Oracle package syntax provided only by EDB Postgres Advanced Server.

STRICT

The STRICT keyword specifies for the function not to execute if called with a NULL argument. Instead the function returns NULL.

LEAKPROOF

The LEAKPROOF keyword specifies for the function not to reveal any information about arguments other than through a return value.

PARALLEL { UNSAFE | RESTRICTED | SAFE }

The PARALLEL clause enables the use of parallel sequential scans (parallel mode). A parallel sequential scan uses multiple workers to scan a relation in parallel during a query in contrast to a serial sequential scan.

When set to UNSAFE, the procedure or function can't be executed in parallel mode. The presence of such a procedure or function forces a serial execution plan. This is the default setting if you omit the PARALLEL clause.

When set to RESTRICTED, the procedure or function can be executed in parallel mode, but the execution is restricted to the parallel group leader. If the qualification for any particular relation has anything that is parallel restricted, that relation won't be chosen for parallelism.

When set to SAFE, the procedure or function can be executed in parallel mode without restriction.

execution_cost

execution_cost specifies a positive number giving the estimated execution cost for the function, in units of cpu_operator_cost. If the function returns a set, this is the cost per returned row. The default is 0.0025.

result_rows

result_rows is the estimated number of rows for the query planner to expect the function to return. The default is 1000.

SET

Use the SET clause to specify a parameter value for the duration of the function:

config_param specifies the parameter name.

value specifies the parameter value.

FROM CURRENT guarantees that the parameter value is restored when the function ends.

package_initializer

The statements in the package_initializer are executed once per user session when the package is first referenced.

Note

The STRICT, LEAKPROOF, PARALLEL, COST, ROWS and SET keywords provide extended functionality for EDB Postgres Advanced Server. Oracle doesn't support them.

← Prev

Working with packages

↑ Up

Working with packages

Next →

Viewing packages and package body definition