ALTER MODULE statement

The ALTER MODULE statement alters the definition of a module.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include ownership of the module and also include all of the privileges necessary to invoke the SQL statements that are specified within the ALTER MODULE statement.

Syntax

module-condition-definition

module-object-identification

module-function-designator

module-procedure-designator

Description

module-name

Identifies the module to be altered. The module-name must identify a module that exists at the current server (SQLSTATE 42704). The specified name must not be an alias for a module (SQLSTATE 560CT).

ADD

Adds an object to the module or adds the body to a routine definition that already exists in the module without a body. If adding a user-defined type or a global variable, the object must not identify a user-defined type or global variable that already exists in the module. If the user-defined type or global variable did not exist, it is added to the module for use within the module only.

If adding a routine and the specified routine does not exist, the routine is added. If adding a routine and the specified routine exists, the existing routine definition must not include a routine body (SQLSTATE 42723). This routine prototype is completely replaced by the new routine definition, including the routine attributes and the routine body, except that the published attribute is retained. The specified routine is considered to exist if one of the following conditions is true:

There is a routine in the module with the same specific name and same routine name.
The specified routine is a procedure and there is a procedure in the module with the same procedure name and the same number of parameters. The names and data types of the parameters do not need to match.
The specified routine is a function and there is a function in the module with the same function name and the same number of parameters with matching data types. The length, precision, and scale of parameter data types are not compared and can be different when determining if the specified routine exists. The names of the parameters do not need to match.

module-condition-definition

Adds a module condition.

condition-name

Name of the condition. The name must not identify an existing condition in the module. The condition-name must be specified without any qualification (SQLSTATE 42601). The name of the condition must be unique within the module.

FOR SQLSTATE string-constant

Specifies the SQLSTATE that is associated with the condition. The string-constant must be specified as five characters enclosed in single quotation marks, and the SQLSTATE class (the first two characters) must not be '00'. This is an optional clause.

module-function-definition

The syntax to add a function is the same as the CREATE FUNCTION statement excluding the CREATE keyword and both the function-name and specific-name must be specified without any qualification (SQLSTATE 42601). If the function is unique within the module, a new function is added. If the function matches an existing function that does not include a body (SQL-routine-body or EXTERNAL NAME clause), then this function prototype is replaced by the new definition except that the published attribute is retained.

The module function definition must not specify the SOURCE clause, the TEMPLATE clause, or the LANGUAGE OLEDEB option (SQLSTATE 42613).

module-procedure-definition

The syntax to define the procedure is the same as the CREATE PROCEDURE statement excluding the CREATE keyword and both the procedure-name and specific-name must be specified without any qualification (SQLSTATE 42601). If the procedure signature is unique within the module, a new procedure is added. If the procedure matches an existing procedure that does not include a body (SQL-routine-body or EXTERNAL NAME clause), then this procedure prototype is replaced by the new definition except that the published attribute is retained. The name of the procedure can begin with SYS_ only to add the module initialization procedure called SYS_INIT. See Notes for details.

module-type-definition

The syntax to define the user-defined type is the same as the CREATE TYPE statement excluding the CREATE keyword and the type-name must be specified without any qualification (SQLSTATE 42601). The name of the user-defined type must be unique within the module. A structured type cannot be defined in a module. Any generated functions required to support the type definition are also defined in the module. If the module user-defined type is published then so are the generated functions.

module-variable-definition

DROP

Drops a specified part of a module. The module-object-identification syntax is used to identify the object to be dropped unless the body of the module is being dropped.

BODY

Drops the module body, which includes:

all objects that are not published.
the routine body of any published SQL routines
the EXTERNAL reference for any published external routines.

PUBLISH

Adds a new object to the module and makes it available for use outside the module. In the case of routines, a routine prototype can be specified that does not include the executable body of the routine.

module-condition-definition

Adds a module condition that is available for use outside the module.

condition-name

FOR SQLSTATE string-constant

module-function-definition

The syntax to define the function is the same as the CREATE FUNCTION statement excluding the CREATE keyword and both the function-name and specific-name must be specified without any qualification (SQLSTATE 42601). The definition of the function must include the function name, full specification of any parameters and the returns clause. Module user-defined data types that are not published are not candidates for the parameter data types or the RETURNS clause data type. Module variables that are not published are not candidates for the anchor object in an ANCHOR clause of a parameter data type or a returns data type. A function prototype can be specified by omitting the LANGUAGE clause (or specifying LANGUAGE SQL) and the SQL-routine-body. The function signature must be unique within the module. The name of the function must not begin with "SYS_" (SQLSTATE 42939). All SQL functions added to a module are processed as if a compound SQL (compiled) statement was used.

The module function definition can only specify the RETURNS TABLE clause when the SQL-routine-body is an compound SQL (compiled) statement that specifies NOT ATOMIC. The module function definition must not specify the SOURCE clause, the TEMPLATE clause, or the LANGUAGE OLEDEB option (SQLSTATE 42613).

module-procedure-definition

The syntax to define the procedure is the same as the CREATE PROCEDURE statement excluding the CREATE keyword and both the procedure-name and specific-name must be specified without any qualification (SQLSTATE 42601). The definition of the procedure must include the procedure name and full specification of any parameters. Module user-defined data types that are not published are not candidates for the parameter data types. Module variables that are not published are not candidates for the anchor object in an ANCHOR clause of a parameter definition. A function prototype can be specified by omitting the LANGUAGE clause (or specifying LANGUAGE SQL) and the SQL-routine-body. The procedure signature must be unique within the module. The name of the procedure must not begin with "SYS_" (SQLSTATE 42939).

module-type-definition

The syntax to define the user-defined type is the same as the CREATE TYPE statement excluding the CREATE keyword and the type-name must be specified without any qualification (SQLSTATE 42601). Module user-defined data types that are not published are not candidates for any data type referenced in the module user-defined data type definition. Module variables that are not published are not candidates for the anchor object in an ANCHOR clause. The name of the user-defined type must not begin with "SYS_" (SQLSTATE 42939) and must be unique within the module. A structured type cannot be defined in a module. Any generated functions required to support the type definition are also defined in the module as published functions.

module-variable-definition

The syntax to define the variable is the same as the CREATE VARIABLE statement excluding the CREATE keyword and the variable-name must be specified without any qualification (SQLSTATE 42601). Module user-defined data types that are not published are not candidates for the any data type referenced in the variable definition. Module variables that are not published are not candidates for the anchor object in an ANCHOR clause. The name of the variable must not begin with "SYS_" (SQLSTATE 42939) and must be unique within the module.

module-object-identification

Identifies a unique module object.

module-function-designator

Uniquely identifies a single module function.

FUNCTION unqualified-function-name

Identifies a particular function, and is valid only if there is exactly one function instance with the name unqualified-function-name in the module. The identified function can have any number of parameters defined for it. If no function by this name exists in the module, an error (SQLSTATE 42704) is raised. If there is more than one instance of the function in the module, an error (SQLSTATE 42725) is raised.

FUNCTION unqualified-function-name (data type,...)

Provides the function signature, which uniquely identifies the function. The function resolution algorithm is not used.

unqualified-function-name

Specifies the name of the function.

data-type

Values must match the data types that were specified (in the corresponding position) when the function was originally defined. The number of data types, and the logical concatenation of the data types, is used to identify the specific function instance.

If a data type is unqualified, the type name is resolved by searching the schemas on the SQL path. This also applies to data type names specified for a REFERENCE type.

It is not necessary to specify the length, precision, or scale for the parameterized data types. Instead, an empty set of parentheses can be coded to indicate that these attributes are to be ignored when looking for a data type match. FLOAT() cannot be used (SQLSTATE 42601), because the parameter value indicates different data types (REAL or DOUBLE). If length, precision, or scale is coded, the value must exactly match that specified when the function was defined.

A type of FLOAT(n) does not need to match the defined value for n, because 0 < n < 25 means REAL, and 24 < n < 54 means DOUBLE. Matching occurs on the basis of whether the type is REAL or DOUBLE. If no function with the specified signature exists in the module, an error (SQLSTATE 42883) is raised.

SPECIFIC FUNCTION unqualified-specific-name

Identifies a particular user-defined function, using the name that is specified or defaulted to at function definition time. The unqualified-specific-name must identify a specific function instance in the module; otherwise, an error is returned (SQLSTATE 42704).

module-procedure-designator

Uniquely identifies a single module procedure.

PROCEDURE unqualified-procedure-name

Identifies a particular procedure, and is valid only if there is exactly one procedure instance with the name unqualified-procedure-name in the module. The identified procedure can have any number of parameters defined for it. If no procedure by this name exists in the module, an error is returned (SQLSTATE 42704). If there is more than one instance of the procedure in the module, an error is returned (SQLSTATE 42725).

PROCEDURE unqualified-procedure-name (data-type

Provides the procedure signature, which uniquely identifies the procedure. The procedure resolution algorithm is not used.

unqualified-procedure-name

Specifies the name of the procedure.

data-type

Values must match the data types that were specified (in the corresponding position) when the procedure was originally defined. The number of data types, and the logical concatenation of the data types, is used to identify the specific procedure instance.

If a data type is unqualified, the type name is resolved by searching the schemas on the SQL path. This also applies to data type names specified for a REFERENCE type.

FLOAT() cannot be used (SQLSTATE 42601), because the parameter value indicates different data types (REAL or DOUBLE). If length, precision, or scale is coded, the value must exactly match that specified in when the procedure was defined.

A type of FLOAT(n) does not need to match the defined value for n, because 0 < n < 25 means REAL, and 24 < n < 54 means DOUBLE. Matching occurs on the basis of whether the type is REAL or DOUBLE.

If no procedure with the specified signature exists in the module, an error is returned (SQLSTATE 42883).

SPECIFIC PROCEDURE unqualified-specific-name

Identifies a particular procedure, using the name that is specified or defaulted to at procedure definition time. The unqualified-specific-name must identify a specific procedure instance in the module; otherwise, an error is returned (SQLSTATE 42704).

TYPE type-name

Identifies a user-defined type from the module. The type-name must be specified without any qualification (SQLSTATE 42601) and must identify a user-defined type that exists in the module (SQLSTATE 42704).

VARIABLE variable-name

Identifies a global variable from the module. The variable-name must be specified without any qualification (SQLSTATE 42601) and must identify a global variable that exists in the module (SQLSTATE 42704).

CONDITION condition-name

Identifies a condition from the module. The condition-name must be specified without any qualification and must identify a condition that exists in the module (SQLSTATE 42737).

Rules

Names of objects in the module cannot begin with "SYS_" with the exception of specifically designated SYS_INIT procedure name (SQLSTATE 42939).
ALTER MODULE DROP FUNCTION: If the function is referenced in the definition of a row permission or column mask, the function cannot be dropped (SQLSTATE 42893).
ALTER MODULE DROP VARIABLE: If the variable is referenced in the definition of a row permission or column mask, the variable cannot be dropped (SQLSTATE 42893).
ALTER MODULE DROP BODY: If the module is referenced in the definition of a row permission or column mask, the module cannot be dropped (SQLSTATE 42893).

Notes

Module initialization: A module can have a special initialization procedure called SYS_INIT that is implicitly executed when the first reference is made to a module routine or module global variable. The SYS_INIT procedure must be implemented with no parameters, cannot return result sets, and can be an SQL or external procedure that cannot be published (SQLSTATE 428HP). If the SYS_INIT procedure fails, an error is returned for the statement that caused the module initialization (SQLSTATE 56098).
Use of module conditions: A module condition can only be used with a SIGNAL statement, RESIGNAL statement or a handler declaration that is within a compound SQL (compiled) statement.
Invalidation: If a routine prototype is replaced using the ADD action, all objects that depended on the published module routine are invalidated. If DROP BODY is issued, all objects dependent on published module routines are invalidated.
Obfuscation: The ALTER MODULE ADD FUNCTION, ALTER MODULE ADD PROCEDURE, ALTER MODULE PUBLISH FUNCTION, and ALTER MODULE PUBLISH PROCEDURE statements can be submitted in obfuscated form. In an obfuscated statement, only the routine name and its parameters are readable. The rest of the statement is encoded in such a way that is not readable but can be decoded by the database server. Obfuscated statements can be produced by calling the DBMS_DDL.WRAP function.

Example

The following statements create a module named INVENTORY containing an associative array type, a variable of that data type, a procedure that adds elements to the array and a function that extracts elements from the array. Only the function and the procedure can be referenced from outside of the module based on the PUBLISH keyword in the corresponding ALTER MODULE statements. The data type and the variable can only be referenced by other objects in the module.

     CREATE MODULE INVENTORY

     ALTER MODULE INVENTORY ADD
     TYPE ITEMLIST AS INTEGER ARRAY[VARCHAR(100)]

     ALTER MODULE INVENTORY ADD
     VARIABLE ITEMS ITEMLIST

     ALTER MODULE INVENTORY PUBLISH
     PROCEDURE UPDATE_ITEM(NAME VARCHAR(100), QUANTITY INTEGER)
     BEGIN
     SET ITEMS[NAME] = QUANTITY;
     END

     ALTER MODULE INVENTORY PUBLISH
     FUNCTION CHECK_ITEM(NAME VARCHAR(100)) RETURNS INTEGER
     RETURN ITEMS[NAME]