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
>>-ALTER MODULE--module-name------------------------------------>
>--+-ADD--+-module-condition-definition-+-------+--------------><
| +-module-function-definition--+ |
| +-module-procedure-definition-+ |
| +-module-type-definition------+ |
| '-module-variable-definition--' |
+-DROP--+-BODY-----------------------------+-+
| '-| module-object-identification |-' |
'-PUBLISH--+-module-condition-definition-+---'
+-module-function-definition--+
+-module-procedure-definition-+
+-module-type-definition------+
'-module-variable-definition--'
module-condition-definition
|--CONDITION--condition-name------------------------------------>
>--+-----------------------------------------------+------------|
| .-VALUE-. |
| .-SQLSTATE--+-------+-. |
'-FOR--+---------------------+--string-constant-'
module-object-identification
|--+-| module-function-designator |--+--------------------------|
+-| module-procedure-designator |-+
+-CONDITION--condition-name-------+
+-TYPE--type-name-----------------+
'-VARIABLE--variable-name---------'
module-function-designator
|--+-FUNCTION--unqualified-function-name--+-------------------------+-+--|
| '-(--+---------------+--)-' |
| | .-,---------. | |
| | V | | |
| '---data-type-+-' |
'-SPECIFIC FUNCTION--unqualified-specific-name---------------------'
module-procedure-designator
|--+-PROCEDURE--unqualified-procedure-name--+-------------------------+-+--|
| '-(--+---------------+--)-' |
| | .-,---------. | |
| | V | | |
| '---data-type-+-' |
'-SPECIFIC PROCEDURE--unqualified-specific-name----------------------'
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.
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). 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
- 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). The name
of the variable must
be unique within the module.
- 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
- 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 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.
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 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]