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