Deletes a module from an Oracle Rdb database.
1 – Environment
You can use the DROP MODULE statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1;4mDROP[m[1m [1;4mMODULE[m[1m qq> <module-name> qwqwqqqqqqqqqqqqqqqwqwq>[m
[1m [m [1mx tq> [1;4mRESTRICT[m [1mqqu x [m
[1m [m [1mx tq> [1;4mCASCADE[m[1m [m [1mqqu x [m
[1m [m [1mx mq> [1;4mIF[m[1m [1;4mEXISTS[m[1m qqj x [m
[1mmqqqqqqqqqq<qqqqqqqqj[m
3 – Arguments
3.1 – CASCADE
Specifies that you want SQL to invalidate all objects that
refer to routines in the module and then delete that module
definition. This is known as a cascading delete. If you delete a
module referenced by a stored routine with a routine or language-
semantic dependency, SQL also marks the affected stored routine
as invalid.
3.2 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
3.3 – module-name
Identifies the name of the module.
3.4 – RESTRICT
Prevents the removal of a stored routine definition when the
function or procedure is referenced by any other object within an
Oracle Rdb database. RESTRICT is the default.
4 – Examples
Example 1: Removing a module from an Oracle Rdb database
SQL> DROP MODULE employee_salary;
Example 2: Observing the DROP MODULE ... CASCASE action
This example demonstrates that dependencies may exists between
the module being dropped and other database objects such as
routines and triggers. The script uses SET FLAGS with the WARN_
INVALID option so that the database administrator is informed
of any affected objects. In this case a rollback is used to
undo the DROP MODULE ... CASCADE as the affects might damage
the application environment.
SQL> start transaction read write;
SQL>
SQL> create module FIRST_MODULE
cont> function GET_TIME ()
cont> returns TIME (2);
cont> return CURRENT_TIME (2);
cont> end module;
SQL>
SQL> create module SECOND_MODULE
cont> procedure PRINT_TRACE (in :arg varchar(40));
cont> begin
cont> trace GET_TIME(), ': ', :arg;
cont> end;
cont> end module;
SQL>
SQL> create table SAMPLE_TABLE
cont> (ident integer,
cont> descr char(100));
SQL> create trigger SAMPLE_TABLE_TRIGGER
cont> after insert on SAMPLE_TABLE
cont> (trace GET_TIME(), ': ', SAMPLE_TABLE.descr)
cont> for each row;
SQL>
SQL> commit;
SQL>
SQL> set flags 'warn_invalid';
SQL> drop module FIRST_MODULE restrict;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-E-OBJ_INUSE, object "GET_TIME" is referenced by SAMPLE_TABLE_TRIGGER. (usage: Trigger)
-RDMS-E-MODNOTDEL, module "FIRST_MODULE" has not been deleted
SQL> drop module FIRST_MODULE cascade;
~Xw: Trigger "SAMPLE_TABLE_TRIGGER" marked invalid
~Xw: Routine "PRINT_TRACE" marked invalid
SQL>
SQL> rollback;