5.11. PACKAGE

A package is a group of procedures and functions managed as one entity.

5.11.1. CREATE PACKAGE

Creates a package header

Available inDSQL

Syntax 

   |CREATE PACKAGE package_name
   |[SQL SECURITY {INVOKER | DEFINER}]
   |AS
   |BEGIN
   |  [ <package_item> ... ]
   |END
   | 
   |<package_item> ::=
   |    <function_decl>;
   |  | <procedure_decl>;
   | 
   |<function_decl> ::=
   |  FUNCTION funcname [ ( [ <in_params> ] ) ]
   |  RETURNS <domain_or_non_array_type> [COLLATE collation]
   |  [DETERMINISTIC]
   | 
   |<procedure_decl> ::=
   |  PROCEDURE procname [ ( [ <in_params> ] ) ]
   |  [RETURNS (<out_params>)]
   | 
   |<in_params> ::= <inparam> [, <inparam> ... ]
   | 
   |<inparam> ::= <param_decl> [ { = | DEFAULT } <value> ]
   | 
   |<out_params> ::= <outparam> [, <outparam> ...]
   | 
   |<outparam> ::= <param_decl>
   | 
   |<value> ::= { literal | NULL | context_var }
   | 
   |<param-decl> ::= paramname <domain_or_non_array_type> [NOT NULL]
   |  [COLLATE collation]
   | 
   |<domain_or_non_array_type> ::=
   |  !! See Scalar Data Types Syntax !!

Table 5.30. CREATE PACKAGE Statement Parameters
ParameterDescription

package_name

Package name. The maximum length is 63 characters. The package name must be unique among all package names.

function_decl

Function declaration

procedure_decl

Procedure declaration

func_name

Function name. The maximum length is 63 characters. The function name must be unique within the package.

proc_name

Procedure name. The maximum length is 63 characters. The function name must be unique within the package.

collation

Collation

inparam

Input parameter declaration

outparam

Output parameter declaration

literal

A literal value that is assignment-compatible with the data type of the parameter

context_var

Any context variable that is assignment-compatible with the data type of the parameter

paramname

The name of an input parameter of a procedure or function, or an output parameter of a procedure. The maximum length is 63 characters. The name of the parameter must be unique among input and output parameters of the procedure or function.

The CREATE PACKAGE statement creates a new package header. Routines (procedures and functions) declared in the package header are available outside the package using the full identifier (package_name.proc_name or package_name.func_name). Routines defined only in the package body — but not in the package header — are not visible outside the package.

ⓘ
Package procedure and function names may shadow global routines

If a package header or package body declares a procedure or function with the same name as a stored procedure or function in the global namespace, it is not possible to call that global procedure or function from the package body. In this case, the procedure or function of the package will always be called.

For this reason, it is recommended that the names of stored procedures and functions in packages do not overlap with names of stored procedures and functions in the global namespace.

5.11.1.1. Statement Terminators

Some SQL statement editors — specifically the isql utility that comes with Firebird, and possibly some third-party editors — employ an internal convention that requires all statements to be terminated with a semicolon. This creates a conflict with PSQL syntax when coding in these environments. If you are unacquainted with this problem and its solution, please study the details in the PSQL chapter in the section entitled Switching the Terminator in isql.

5.11.1.2. SQL Security

The SQL SECURITY clause specifies the security context for executing other routines or inserting into other tables from functions or procedures defined in this package. When SQL Security is not specified, the default value of the database is applied at runtime.

The SQL SECURITY clause can only be specified for the package, not for individual procedures and functions of the package.

See also SQL Security in chapter Security.

5.11.1.3. Procedure and Function Parameters

For details on stored procedure parameters, see Section 5.8.1.2, “Parameters” in Section 5.8.1, “CREATE PROCEDURE”.

For details on function parameters, see Section 5.9.1.2, “Parameters” in Section 5.9.1, “CREATE FUNCTION”.

5.11.1.4. Who Can Create a Package

The CREATE PACKAGE statement can be executed by:

The user who created the package header becomes its owner.

5.11.1.5. Examples of CREATE PACKAGE

  1. Create a package header

  |CREATE PACKAGE APP_VAR
  |AS
  |BEGIN
  |  FUNCTION GET_DATEBEGIN() RETURNS DATE DETERMINISTIC;
  |  FUNCTION GET_DATEEND() RETURNS DATE DETERMINISTIC;
  |  PROCEDURE SET_DATERANGE(ADATEBEGIN DATE,
  |      ADATEEND DATE DEFAULT CURRENT_DATE);
  |END

  1. With DEFINER set for package pk, user US needs only the EXECUTE privilege on pk. If it were set for INVOKER, either the user or the package would also need the INSERT privilege on table t.

       |create table t (i integer);
   |set term ^;
   |create package pk SQL SECURITY DEFINER
   |as
   |begin
   |    function f(i integer) returns int;
   |end^
   | 
   |create package body pk
   |as
   |begin
   |    function f(i integer) returns int
   |    as
   |    begin
   |      insert into t values (:i);
   |      return i + 1;
   |    end
   |end^
   |set term ;^
   |grant execute on package pk to user us;
   | 
   |commit;
   | 
   |connect 'localhost:/tmp/69.fdb' user us password 'pas';
   |select pk.f(3) from rdb$database;

See alsoCREATE PACKAGE BODY, RECREATE PACKAGE BODY, Section 5.11.2, “ALTER PACKAGE”, Section 5.11.4, “DROP PACKAGE”, Section 5.11.5, “RECREATE PACKAGE”

5.11.2. ALTER PACKAGE

Alters a package header

Available inDSQL

Syntax 

  |ALTER PACKAGE package_name
  |[SQL SECURITY {INVOKER | DEFINER}]
  |AS
  |BEGIN
  |  [ <package_item> ... ]
  |END
  | 
  |!! See syntax of CREATE PACKAGE for further rules!!

The ALTER PACKAGE statement modifies the package header. It can be used to change the number and definition of procedures and functions, including their input and output parameters. However, the source and compiled form of the package body is retained, though the body might be incompatible after the change to the package header. The validity of a package body for the defined header is stored in the column RDB$PACKAGES.RDB$VALID_BODY_FLAG.

Altering a package without specifying the SQL SECURITY clause will remove the SQL Security property if currently set for this package. This means the behaviour will revert to the database default.

5.11.2.1. Who Can Alter a Package

The ALTER PACKAGE statement can be executed by:

  • Administrators

  • The owner of the package

  • Users with the ALTER ANY PACKAGE privilege

5.11.2.2. Examples of ALTER PACKAGE

Modifying a package header 

  |ALTER PACKAGE APP_VAR
  |AS
  |BEGIN
  |  FUNCTION GET_DATEBEGIN() RETURNS DATE DETERMINISTIC;
  |  FUNCTION GET_DATEEND() RETURNS DATE DETERMINISTIC;
  |  PROCEDURE SET_DATERANGE(ADATEBEGIN DATE,
  |      ADATEEND DATE DEFAULT CURRENT_DATE);
  |END

See alsoSection 5.11.1, “CREATE PACKAGE”, Section 5.11.4, “DROP PACKAGE”, RECREATE PACKAGE BODY

5.11.3. CREATE OR ALTER PACKAGE

Creates a package header if it does not exist, or alters a package header

Available inDSQL

Syntax 

  |CREATE OR ALTER PACKAGE package_name
  |[SQL SECURITY {INVOKER | DEFINER}]
  |AS
  |BEGIN
  |  [ <package_item> ... ]
  |END
  | 
  |!! See syntax of CREATE PACKAGE for further rules!!

The CREATE OR ALTER PACKAGE statement creates a new package or modifies an existing package header. If the package header does not exist, it will be created using CREATE PACKAGE. If it already exists, then it will be modified using ALTER PACKAGE while retaining existing privileges and dependencies.

5.11.3.1. Examples of CREATE OR ALTER PACKAGE

Creating a new or modifying an existing package header 

  |CREATE OR ALTER PACKAGE APP_VAR
  |AS
  |BEGIN
  |  FUNCTION GET_DATEBEGIN() RETURNS DATE DETERMINISTIC;
  |  FUNCTION GET_DATEEND() RETURNS DATE DETERMINISTIC;
  |  PROCEDURE SET_DATERANGE(ADATEBEGIN DATE,
  |      ADATEEND DATE DEFAULT CURRENT_DATE);
  |END

See alsoSection 5.11.1, “CREATE PACKAGE”, Section 5.11.2, “ALTER PACKAGE”, Section 5.11.5, “RECREATE PACKAGE”, RECREATE PACKAGE BODY

5.11.4. DROP PACKAGE

Drops a package header

Available inDSQL

Syntax 

  |DROP PACKAGE package_name

Table 5.31. DROP PACKAGE Statement Parameters
ParameterDescription

package_name

Package name

The DROP PACKAGE statement deletes an existing package header. If a package body exists, it will be dropped together with the package header. If there are still dependencies on the package, an error will be raised.

5.11.4.1. Who Can Drop a Package

The DROP PACKAGE statement can be executed by:

  • Administrators

  • The owner of the package

  • Users with the DROP ANY PACKAGE privilege

5.11.4.2. Examples of DROP PACKAGE

Dropping a package header 

  |DROP PACKAGE APP_VAR

See alsoSection 5.11.1, “CREATE PACKAGE”, DROP PACKAGE BODY

5.11.5. RECREATE PACKAGE

Drops a package header if it exists, and creates a package header

Available inDSQL

Syntax 

  |RECREATE PACKAGE package_name
  |[SQL SECURITY {INVOKER | DEFINER}]
  |AS
  |BEGIN
  |  [ <package_item> ... ]
  |END
  | 
  |!! See syntax of CREATE PACKAGE for further rules!!

The RECREATE PACKAGE statement creates a new package or recreates an existing package header. If a package header with the same name already exists, then this statement will first drop it and then create a new package header. It is not possible to recreate the package header if there are still dependencies on the existing package, or if the body of the package exists. Existing privileges of the package itself are not preserved, nor are privileges to execute the procedures or functions of the package.

5.11.5.1. Examples of RECREATE PACKAGE

Creating a new or recreating an existing package header 

  |RECREATE PACKAGE APP_VAR
  |AS
  |BEGIN
  |  FUNCTION GET_DATEBEGIN() RETURNS DATE DETERMINISTIC;
  |  FUNCTION GET_DATEEND() RETURNS DATE DETERMINISTIC;
  |  PROCEDURE SET_DATERANGE(ADATEBEGIN DATE,
  |      ADATEEND DATE DEFAULT CURRENT_DATE);
  |END

See alsoSection 5.11.1, “CREATE PACKAGE”, Section 5.11.4, “DROP PACKAGE”, CREATE PACKAGE BODY, RECREATE PACKAGE BODY