Alters a module to add or drop routines, change a comment, or
compile stored routines.
1 – Environment
You can use the ALTER MODULE statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
ALTER MODULE <module-name> -+-> alter-module-clauses -+-+----------------+--->
+-----------<-------------+ +-> END MODULE --+
alter-module-clauses =
-+-> ADD routine-clause ---------------+->
+-> COMMENT IS +-> '<text-literal>' -++
| +------- / <----------+|
+-> COMPILE --------------------------+
+-> drop-routine-clause --------------+
+-> RENAME TO <new-module-name> ------+
drop-routine-clause =
DROP -+-> FUNCTION --+> <routine-name> -+-+---------------+-+->
+-> PROCEDURE -+ | +-> CASCADE ----+ |
| +-> RESTRICT --+ |
| +-> IF EXISTS --+ |
+----------<--------+
3 – Arguments
3.1 – ADD routine-clause
Allows new functions and procedures to be added to the module.
Refer to the CREATE MODULE for details on the routine-clause. The
END MODULE clause must be used to end the ALTER MODULE clause to
provide an unambiguous statement termination.
3.2 – COMMENT_IS
Adds a comment about the module. Enclose the comment within
single quotation marks (') and separate multiple lines in a
comment with a slash mark (/). This clause is equivalent to the
COMMENT ON MODULE statement.
3.3 – COMPILE
Recompiles stored routines in the module. Any that were
marked invalid will have this flag cleared if the compile was
successful.
3.4 – drop-routine-clause
The DROP FUNCTION and DROP PROCEDURE clauses will drop the named
routines from this module. All DROP clauses are executed prior to
the COMPILE and ADD clauses in this ALTER statement.
3.5 – END_MODULE
This terminating clause is required when using ADD FUNCTION or
ADD PROCEDURE since there is no way to distinguish between the
end of a compound statement and the end of the ALTER MODULE
statement.
3.6 – RENAME_TO
Changes the name of the module being altered. See the RENAME for
further discussion. If the new name is the name of a synonym then
an error will be raised.
The RENAME TO clause requires synonyms be enabled for this
database. Refer to the ALTER DATABASE SYNONYMS ARE ENABLED
clause. Note that these synonyms may be deleted if they are no
longer used by database definitions or applications.
The old name will be used to create a synonym for the new name of
this module. This synonym can be dropped if the name is no longer
used by applications.
4 – Examples
Example 1: Changing the comment on a module
A comment can be added or changed on a module using the COMMENT
IS clause as shown in this example.
SQL> alter module EMPLOYEES_MAINTENANCE
cont> comment is
cont> 'routines to add and remove employee rows'
cont> / 'Fix: also record the employees birthday';
SQL>
SQL> show module EMPLOYEES_MAINTENANCE;
Information for module EMPLOYEES_MAINTENANCE
Header:
EMPLOYEES_MAINTENANCE
Comment: routines to add and remove employee rows
Fix: also record the employees birthday
Module ID is: 7
Routines in module EMPLOYEES_MAINTENANCE:
ADD_EMPLOYEE
IS_CURRENT_EMPLOYEE
REMOVE_EMPLOYEE
Example 2: Revalidating all routines in a module
The COMPILE clause can be used to check each stored procedure or
function to ensure that it can be executed. If the compile fails
it will report the first reason, in this example a missing table.
SQL> alter module EMPLOYEES_MAINTENANCE compile;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDB-E-OBSOLETE_METADA, request references metadata objects that no longer exist
-RDMS-F-BAD_SYM, unknown relation symbol - ARCHIVE_EMPLOYEES
Example 3: Replacing a routine in a module
The following example creates a simple module and shows the
effect of DROP TABLE . . . CASCADE. That is, the procedure REMOVE_
EMPLOYEE is marked as invalid. The COMPILE clause is used to
attempt to re-validate the procedure, however, a referenced table
no longer exists. After replacing the table the COMPILE completes
successfully.
SQL> set dialect 'sql99';
SQL> attach 'file PERSONNEL1';
SQL>
SQL> create table EMPLOYEES
cont> (employee_id integer,
cont> last_name char(40),
cont> first_name char(40),
cont> birthday date,
cont> start_date date default current_date);
SQL>
SQL> create table ARCHIVE_EMPLOYEES
cont> (employee_id integer,
cont> last_name char(40),
cont> first_name char(40),
cont> archive_date date default current_date);
SQL>
SQL> create module EMPLOYEES_MAINTENANCE
cont>
cont> procedure REMOVE_EMPLOYEE (in :employee_id integer);
cont> begin
cont> -- take copy of the old row
cont> insert into ARCHIVE_EMPLOYEES
cont> (employee_id, last_name, first_name)
cont> select employee_id, last_name, first_name
cont> from EMPLOYEES
cont> where employee_id = :employee_id;
cont> -- remove the old row
cont> delete from EMPLOYEES
cont> where employee_id = :employee_id;
cont> end;
cont>
cont> procedure ADD_EMPLOYEE
cont> (in :employee_id integer,
cont> in :last_name char(40),
cont> in :first_name char(40),
cont> in :birthday date);
cont> insert into EMPLOYEES
cont> (employee_id, last_name, first_name, birthday)
cont> values (:employee_id, :last_name, :first_name, :birthday);
cont>
cont> end module;
SQL>
SQL> show module EMPLOYEES_MAINTENANCE
Information for module EMPLOYEES_MAINTENANCE
Header:
EMPLOYEES_MAINTENANCE
Module ID is: 7
Routines in module EMPLOYEES_MAINTENANCE:
ADD_EMPLOYEE
REMOVE_EMPLOYEE
SQL>
SQL> drop table ARCHIVE_EMPLOYEES cascade;
SQL>
SQL> show procedure REMOVE_EMPLOYEE;
Information for procedure REMOVE_EMPLOYEE
Current state is INVALID
Can be revalidated
Procedure ID is: 8
Source:
REMOVE_EMPLOYEE (in :employee_id integer);
begin
-- take copy of the old row
insert into ARCHIVE_EMPLOYEES
(employee_id, last_name, first_name)
select employee_id, last_name, first_name
from EMPLOYEES
where employee_id = :employee_id;
-- remove the old row
delete from EMPLOYEES
where employee_id = :employee_id;
end
No description found
Module name is: EMPLOYEES_MAINTENANCE
Module ID is: 7
Number of parameters is: 1
Parameter Name Data Type Domain or Type
-------------- --------- --------------
EMPLOYEE_ID INTEGER
Parameter position is 1
Parameter is IN (read)
Parameter is passed by reference
SQL>
SQL> -- COMPILE reports the missing table
SQL> alter module EMPLOYEES_MAINTENANCE compile;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDB-E-OBSOLETE_METADA, request references metadata objects that no longer exist
-RDMS-F-BAD_SYM, unknown relation symbol - ARCHIVE_EMPLOYEES
SQL>
SQL> create table ARCHIVE_EMPLOYEES
cont> (employee_id integer,
cont> last_name char(40),
cont> first_name char(40),
cont> birthday date,
cont> archive_date date default current_date);
SQL>
SQL> -- new table definition is compatible
SQL> alter module EMPLOYEES_MAINTENANCE compile;
SQL>
SQL> alter module EMPLOYEES_MAINTENANCE
cont> comment is
cont> 'routines to add and remove employee rows'
cont> / 'Fix: also record the employees birthday'
cont>
cont> drop procedure REMOVE_EMPLOYEE if exists
cont>
cont> add procedure REMOVE_EMPLOYEE (in :employee_id integer);
cont> begin
cont> -- take copy of the old row
cont> insert into ARCHIVE_EMPLOYEES
cont> (employee_id, last_name, first_name, birthday)
cont> select employee_id, last_name, first_name, birthday
cont> from EMPLOYEES
cont> where employee_id = :employee_id;
cont> -- remove the old row
cont> delete from EMPLOYEES
cont> where employee_id = :employee_id;
cont> end;
cont> end module;
SQL>
SQL> show module EMPLOYEES_MAINTENANCE;
Information for module EMPLOYEES_MAINTENANCE
Header:
EMPLOYEES_MAINTENANCE
Comment: routines to add and remove employee rows
Fix: also record the employees birthday
Module ID is: 7
Routines in module EMPLOYEES_MAINTENANCE:
ADD_EMPLOYEE
REMOVE_EMPLOYEE
Example 4: Adding a new function to a module
In the following example the ADD clause is used to add a new
function to an existing module.
SQL> alter module EMPLOYEES_MAINTENANCE
cont> add function IS_CURRENT_EMPLOYEE (in :employee_id integer)
cont> returns integer;
cont> return (case
cont> when exists (select *
cont> from EMPLOYEES
cont> where employee_id = :employee_id)
cont> then 1
cont> else 0
cont> end);
cont> end module;
SQL>
SQL> show module EMPLOYEES_MAINTENANCE;
Information for module EMPLOYEES_MAINTENANCE
Header:
EMPLOYEES_MAINTENANCE
Comment: routines to add and remove employee rows
Fix: also record the employees birthday
Module ID is: 7
Routines in module EMPLOYEES_MAINTENANCE:
ADD_EMPLOYEE
IS_CURRENT_EMPLOYEE
REMOVE_EMPLOYEE