6.1 Package Components

Table of Contents Previous Next


6 Packages : 6.1 Package 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.
CREATE [ OR REPLACE ] PACKAGE package_name
[ authorization_clause ]
[ 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 [ 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 [, ... ]
package_name is an identifier assigned to the package - each package must have a name unique within the schema.
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.
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 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 zero, one, or more public variables. Public variable definitions must come before procedure or function declarations.
declaration can be any of the following:
REF CURSOR and Cursor Variable Declaration
TYPE Definitions for Records, Collections, and REF CURSORs
The argument mode. IN declares the argument for input only. This is the default. IN OUT allows the argument to receive a value as well as return a value. OUT specifies the argument is for output only.
The data type(s) of an argument. An argument type may 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. A length must not be specified for any base type - for example, specify VARCHAR2, not VARCHAR2(10).
The type of a column is referenced by writing tablename.columnname%TYPE; using this can sometimes help make a procedure independent from changes to the definition of a table.
The DEFAULT clause supplies a default value for an input argument if one is not supplied in the invocation. DEFAULT may not be specified for arguments with modes IN OUT or OUT.
name is the name of the function or procedure.
CREATE [ OR REPLACE ] PACKAGE BODY package_name
[ 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 ]
procedure_body
END [ proc_name ] ;
Where procedure_body :=
[ declaration; ] [, ...]
statement; [...]
{ WHEN exception [OR exception] [...]] THEN statement; }
Where function_definition :=
FUNCTION func_name [ argument_list ]
RETURN rettype [DETERMINISTIC]
[ options_list ]
function_body
END [ func_name ] ;
Where function_body :=
[ declaration; ] [, ...]
statement; [...]
{ 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 :=
COST execution_cost
ROWS result_rows
SET config_param { TO value | = value | FROM CURRENT }
Where package_initializer :=
package_name is the name of the package for which this is the package body. There must be an existing package specification with this 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 zero, one, or more private variables. private_declaration can be any of the following:
REF CURSOR and Cursor Variable Declaration
TYPE Definitions for Records, Collections, and REF CURSORs
A variable, type, or REF CURSOR declaration.
An SPL program statement. Note that a DECLARE - BEGIN - END block is considered an SPL statement unto itself. Thus, the function body may contain nested blocks.
An exception condition name such as NO_DATA_FOUND, OTHERS, etc.
The return data type, which may be any of the types listed for argtype. As for argtype, a length must not be specified for rettype.
Include DETERMINISTIC to specify that the function will 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.
A variable, type, or REF CURSOR declaration.
The argument mode. IN declares the argument for input only. This is the default. IN OUT allows the argument to receive a value as well as return a value. OUT specifies the argument is for output only.
The data type(s) of an argument. An argument type may 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. A length must not be specified for any base type - for example, specify VARCHAR2, not VARCHAR2(10).
The type of a column is referenced by writing tablename.columnname%TYPE; using this can sometimes help make a procedure independent from changes to the definition of a table.
The DEFAULT clause supplies a default value for an input argument if one is not supplied in the procedure call. DEFAULT may not be specified for arguments with modes IN OUT or OUT.
The STRICT keyword specifies that the function will not be executed if called with a NULL argument; instead the function will return NULL.
The LEAKPROOF keyword specifies that the function will not reveal any information about arguments, other than through a return value.
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 is the estimated number of rows that the query planner should expect the function to return. The default is 1000.
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.
The statements in the package_initializer are executed once per user’s session when the package is first referenced.
Please Note: The STRICT, LEAKPROOF, COST, ROWS and SET keywords provide extended functionality for Advanced Server and are not supported by Oracle.

6 Packages : 6.1 Package Components

Table of Contents Previous Next