1 – CATALOG
Deletes the specified catalog definition. You must delete all
schemas and definitions contained in a catalog before you can
delete that catalog. If other definitions exist that refer to the
named catalog, the deletion fails.
The DROP CATALOG statement lists all schemas and definitions that
it is going to delete. You can roll back the statement if you do
not want to delete these definitions.
1.1 – Environment
You can use the DROP CATALOG 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
1.2 – Format
DROP CATALOG <catalog-name> -+-+---------------+-+->
| +-> CASCADE ----+ |
| +-> RESTRICT --+ |
| +-> IF EXISTS --+ |
+----------<--------+
catalog-name =
-+------> <name-of-catalog> --------------+->
| |
+-> " -> <alias>.<name-of-catalog> ->" -+
1.3 – Arguments
1.3.1 – alias.name-of-catalog
Specifies a name for the attachment to the database. Always
qualify the catalog name with an alias if your program or
interactive SQL statements refer to more than one database.
Separate the name of the catalog from the alias with a period,
and enclose the qualified name in double quotation marks.
You must issue a SET QUOTING RULES statement before you can
qualify a catalog name with an alias.
1.3.2 – CATALOG
Syntax options:
CASCADE | RESTRICT
Performs a restricted delete by default. If you prefer to delete
all definitions contained in the catalog, you can specify the
DROP CATALOG CASCADE statement.
1.3.3 – catalog-name
Specifies the module catalog name.
1.3.4 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
1.4 – Example
Example 1: Deleting a catalog
The following statement deletes the catalog DEPT1 associated with
the alias PERSONNEL_ALIAS:
SQL> ATTACH 'ALIAS PERSONNEL_ALIAS FILENAME CORPORATE_DATA';
SQL> SET QUOTING RULES 'SQL99';
SQL> CREATE CATALOG "PERSONNEL_ALIAS.DEPT1";
SQL> SHOW CATALOG;
Catalogs in database PERSONNEL_ALIAS
"PERSONNEL_ALIAS.ADMINISTRATION"
"PERSONNEL_ALIAS.RDB$CATALOG""
"PERSONNEL_ALIAS.DEPT1"
SQL> DROP CATALOG "PERSONNEL_ALIAS.DEPT1";
SQL> SHOW CATALOG;
Catalogs in database PERSONNEL_ALIAS
"PERSONNEL_ALIAS.ADMINISTRATION"
"PERSONNEL_ALIAS.RDB$CATALOG"
SQL> DROP CATALOG "PERSONNEL_ALIAS.RDB$CATALOG";
%SQL-F-NODROPSYSCAT, Catalog "PERSONNEL_ALIAS.RDB$CATALOG" may not be
dropped
SQL>
2 – COLLATING_SEQUENCE
Deletes the named collating sequence.
You cannot delete a collating sequence if it is used by the
database or by any domain in the database.
2.1 – Environment
You can use the DROP COLLATING SEQUENCE 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.2 – Format
DROP COLLATING SEQUENCE --> <collation-name> -+---------------+->
+-> IF EXISTS --+
2.3 – Arguments
2.3.1 – collation-name
Specifies the collation-name argument you used when creating the
collating sequence in the CREATE COLLATING SEQUENCE statement.
2.3.2 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
2.4 – Examples
Example 1: Creating, then deleting, a French collating sequence
The following example creates a collating sequence using the
predefined collating sequence FRENCH. It then uses the SHOW
COLLATING SEQUENCE statement to show the defined collating
sequence.
The example next deletes the collating sequence using the
DROP COLLATING SEQUENCE statement. The SHOW COLLATING SEQUENCE
statement shows that the collating sequence no longer exists.
SQL> ATTACH 'FILENAME personnel';
SQL> CREATE COLLATING SEQUENCE FRENCH FRENCH;
SQL> --
SQL> SHOW COLLATING SEQUENCE
User collating sequences in database with filename personnel
FRENCH
SQL> --
SQL> DROP COLLATING SEQUENCE FRENCH;
SQL> --
SQL> SHOW COLLATING SEQUENCE
User collating sequences in database with filename personnel
No collating sequences found
Example 2: Deleting a collating sequence used to define a domain
or database
The following example shows that you cannot delete a collating
sequence if a domain or database is defined using the collating
sequence:
SQL> CREATE COLLATING SEQUENCE SPANISH SPANISH;
SQL> CREATE DOMAIN LAST_NAME_SPANISH CHAR (14)
cont> COLLATING SEQUENCE IS SPANISH;
SQL> --
SQL> SHOW DOMAIN LAST_NAME_SPANISH
LAST_NAME_SPANISH CHAR(14)
Collating sequence: SPANISH
SQL> --
SQL> SHOW COLLATING SEQUENCE
User collating sequences in database with filename personnel
SPANISH
SQL> --
SQL> -- You cannot delete the collating sequence because the
SQL> -- domain LAST_NAME_SPANISH, defined using SPANISH, still exists:
SQL> --
SQL> DROP COLLATING SEQUENCE SPANISH;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-COLUSEDFLD, the collating sequence named SPANISH is used in
field LAST_NAME_SPANISH
SQL> --
SQL> -- Delete the domain:
SQL> --
SQL> DROP DOMAIN LAST_NAME_SPANISH;
SQL> --
SQL> -- Now you can delete the collating sequence:
SQL> --
SQL> DROP COLLATING SEQUENCE SPANISH;
SQL> --
SQL> SHOW COLLATING SEQUENCE
User collating sequences in database with filename personnel
No collating sequences found
3 – CONSTRAINT
Deletes the named constraints.
3.1 – Environment
You can use the DROP CONSTRAINT 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
3.2 – Format
DROP CONSTRAINT --> <constraint-name> -+---------------+->
+-> IF EXISTS --+
3.3 – Arguments
3.3.1 – constraint-name
Specifies the name of the constraint that you want to delete.
3.3.2 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
3.4 – Example
Example 1: Deleting a constraint
The following statement deletes the SEX_NOT_NULL constraint.
SQL> DROP CONSTRAINT SEX_NOT_NULL;
4 – DATABASE
Deletes a database.
When this statement executes in Oracle Rdb, SQL deletes all
the database root and storage area files associated with the
database.
If you specify a repository path name in the DROP DATABASE
statement or specify an alias for a database attached with the
PATHNAME argument, SQL also deletes the repository directory that
contains the database definitions.
CAUTION
Use the DROP DATABASE statement with care. You cannot use
the ROLLBACK statement to cancel a DROP DATABASE statement.
When you use this statement, SQL deletes the database root
and storage area files, which include all data and all
definitions.
4.1 – Environment
You can use the DROP DATABASE 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
4.2 – Format
DROP DATABASE --------------------------------------------------+
+-------------------------- <----------------------------------+
+-> ALIAS <alias> ---------------------------------------------+-->
+-> FILENAME 'db-attach-spec' ------+-+----------------------+-+
+-> PATHNAME <path-name> -----------+ +-> literal-user-auth -+
db-attach-spec =
--+----------------+-> <file-spec> --->
+-> <node-spec> -+
node-spec =
-+-> <nodename> -+-------------------+-+->
| +-> <access-string> + |
+------------------ :: <--------------+
access-string =
-+-> " <user-name> <password> " --+->
+-> " <VMS-proxy-user-name> " ---+
literal-user-auth =
---> USER '<username>' -+------------------------+-->
+-> USING '<password>' -+
4.3 – Arguments
4.3.1 – ALIAS alias
Specifies the alias for an attached database. The DROP DATABASE
statement deletes the database and all database root and storage
area files associated with the alias.
If the database was declared with the PATHNAME argument, the DROP
DATABASE statement also deletes the repository directory that
contains the database definitions.
4.3.2 – FILENAME
Specifies a quoted string containing full or partial information
needed to access a database. An attach specification contains the
file specification of the .rdb file.
The DROP DATABASE statement deletes the database and all
database system files associated with the database root file
specification. If you use a partial file specification, SQL uses
the standard defaults. The DROP DATABASE statement deletes only
the database files, whether or not there is also a repository
directory containing database definitions.
4.3.3 – literal-user-auth
Specifies the user name and password for access to databases,
particularly remote database.
This literal lets you explicitly provide user name and password
information in the DROP DATABASE statement.
4.3.4 – PATHNAME
Specifies a full or relative repository path name for the
repository directory where the database definitions are stored.
Use a path name instead of a file specification to delete the
repository database definitions from the repository along with
the database root and storage area files. See also the DROP
PATHNAME statement.
4.3.5 – USER username
Defines a character string literal that specifies the operating
system user name that the database system uses for privilege
checking.
4.3.6 – USING password
Defines a character string literal that specifies the user's
password for the user name specified in the USER clause.
4.4 – Examples
Example 1: Deleting files only
The following statement deletes the database system files for
the database associated with the database personnel.rdb. If this
database also had definitions stored in a repository directory,
this DROP DATABASE statement would not delete those definitions.
SQL> DROP DATABASE FILENAME personnel;
Example 2: Deleting files and repository definitions
To delete database files and repository definitions, you
must specify a repository path name in the DROP DATABASE
statement. This statement deletes the repository directory
CDD$TOP.ACCOUNTING.PERSONNEL in addition to all database root
and storage area files associated with it.
SQL> DROP DATABASE PATHNAME CDD$TOP.ACCOUNTING.PERSONNEL;
5 – DOMAIN
Deletes a domain definition. If you attached to the database
using the PATHNAME qualifier, SQL also deletes the domain
definition from the repository.
5.1 – Environment
You can use the DROP DOMAIN 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
5.2 – Format
DROP DOMAIN --> <domain-name> --+---------------+-->
+-> IF EXISTS --+
5.3 – Arguments
5.3.1 – domain-name
Specifies the name of the domain you want to delete.
5.3.2 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
5.4 – Examples
Example 1: Deleting a domain not referred to by columns
SQL> --
SQL> -- The following CREATE DOMAIN statement creates a domain
SQL> -- that is not used by any columns:
SQL> --
SQL> CREATE DOMAIN ABCD IS CHAR(4);
SQL> --
SQL> -- The SHOW DOMAINS statement shows domain ABCD at the
SQL> -- top of the list:
SQL> --
SQL> SHOW DOMAINS
User domains in database with filename personnel
ABCD CHAR(4)
ADDRESS_DATA_1_DOM CHAR(25)
ADDRESS_DATA_2_DOM CHAR(20)
.
.
.
SQL> --
SQL> -- Now delete the domain:
SQL> --
SQL> DROP DOMAIN ABCD;
SQL> --
SQL> -- The SHOW DOMAINS statement shows that the
SQL> -- domain ABCD has been deleted:
SQL> --
SQL> SHOW DOMAINS
User domains in database with filename personnel
ADDRESS_DATA_1_DOM CHAR(25)
ADDRESS_DATA_2_DOM CHAR(20)
.
.
.
Example 2: Deleting a domain referred to by columns
The following example deletes a domain definition. Because a
column refers to the domain definition and a constraint refers
to the column, you must first alter the table before deleting the
domain.
SQL> --
SQL> -- Attempt to delete the domain SEX_DOM. Error messages
SQL> -- indicate that the table EMPLOYEES uses the domain
SQL> -- SEX_DOM, so SEX_DOM cannot yet be deleted:
SQL> --
SQL> DROP DOMAIN SEX_DOM;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-RELEXI, field SEX_DOM is used in relation EMPLOYEES
-RDMS-F-FLDNOTDEL, field SEX_DOM has not been deleted
SQL> --
SQL> -- Looking at the EMPLOYEES table shows that SEX is the
SQL> -- column that depends on the domain SEX_DOM. Try
SQL> -- to delete the column SEX; error messages indicate that the
SQL> -- constraint EMP_SEX_VALUES depends on the column SEX:
SQL> --
SQL> ALTER TABLE EMPLOYEES DROP COLUMN SEX;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-FLDINCON, field SEX is referenced in constraint EMP_SEX_VALUES
-RDMS-F-RELFLDNOD, field SEX has not been deleted
from relation EMPLOYEES
SQL> --
SQL> -- Delete the constraint EMP_SEX_VALUES:
SQL> --
SQL> ALTER TABLE EMPLOYEES DROP CONSTRAINT EMP_SEX_VALUES;
SQL> --
SQL> -- Because EMP_SEX_VALUES was the only constraint or index
SQL> -- that depended on the column SEX, you can now delete
SQL> -- the column SEX:
SQL> --
SQL> ALTER TABLE EMPLOYEES DROP COLUMN SEX;
SQL> --
SQL> -- The column SEX in the table EMPLOYEES was the only column in
SQL> -- the database that depended on the domain SEX_DOM, so you can
SQL> -- now delete the domain SEX_DOM:
SQL> --
SQL> DROP DOMAIN SEX_DOM;
SQL>
6 – FUNCTION
For information on deleting an external or stored function
definition, see the DROP Routine Help topic.
7 – INDEX
Deletes the specified index definition. If you attach to the
database using the PATHNAME qualifier, SQL also deletes the index
definition from the repository.
7.1 – Environment
You can use the DROP INDEX 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
7.2 – Format
DROP INDEX ---> <index-name> --+-+---------------+-+->
| +-> RESTRICT --+ |
| +-> CASCADE --+ |
| +-> IF EXISTS --+ |
+----------<--------+
7.3 – Arguments
7.3.1 – CASCADE
Specifies that you want SQL to modify any storage map that uses
this index to be a NO PLACEMENT VIA INDEX storage map.
7.3.2 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
7.3.3 – index-name
Specifies the name of the index definition you want to delete.
7.3.4 – RESTRICT
Prevents the removal of an index if it is referenced by any other
object within an Oracle Rdb database. RESTRICT is the default.
7.4 – Examples
Example 1: Deleting an index from the default database
SQL> ATTACH 'FILENAME personnel';
SQL> DROP INDEX DEG_COLLEGE_CODE;
SQL> COMMIT;
Example 2: Deleting an index from one of several attached
databases
SQL> ATTACH 'FILENAME personnel';
SQL> ATTACH 'ALIAS MF FILENAME mf_personnel';
SQL> ATTACH 'ALIAS CORP FILENAME corporate_data';
SQL> SET QUOTING RULES 'SQL99';
SQL> DROP INDEX "CORP.ADMINISTRATION".PERSONNEL.EMP_EMPLOYEE_ID;
SQL> COMMIT;
8 – MODULE
Deletes a module from an Oracle Rdb database.
8.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
8.2 – Format
DROP MODULE --> <module-name> -+-+---------------+-+->
| +-> RESTRICT --+ |
| +-> CASCADE --+ |
| +-> IF EXISTS --+ |
+----------<--------+
8.3 – Arguments
8.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.
8.3.2 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
8.3.3 – module-name
Identifies the name of the module.
8.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.
8.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;
9 – OUTLINE
Deletes a query outline.
9.1 – Environment
You can use the DROP OUTLINE 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
The DROP OUTLINE statement allows the user to specify that an
existing outline should be removed from the database.
9.2 – Format
DROP OUTLINE ---> <outline-name> -+---------------+->
+-> IF EXISTS --+
9.3 – Arguments
9.3.1 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
9.3.2 – outline-name
Specifies the name of the outline you want to delete.
9.4 – Examples
Example 1. Deleting an outline
SQL> DROP OUTLINE MY_OUTLINE;
10 – PATHNAME
Deletes the repository definitions. It does not delete the
physical database files.
10.1 – Environment
You can use the DROP PATHNAME 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
10.2 – Format
DROP PATHNAME ---> <path-name> --->
10.3 – Arguments
10.3.1 – path-name
Specifies the repository path name for the schema definitions.
Specify either a full path name or a relative path name. If
you use a relative path name, the current default repository
directory must be defined to include all the path name segments
that precede the relative path name.
10.4 – Examples
Example 1: Deleting a path name with the DROP PATHNAME statement
The following example deletes CDD$TOP.SQL.DEPT3, a repository
directory, and all its descendants. It does not delete the
database system files or data that corresponds to that path name.
SQL> DROP PATHNAME "CDD$TOP.SQL.DEPT3";
11 – PROCEDURE
For information on deleting an external or stored procedure
definition, see the DROP Routine Help topic.
12 – PROFILE
Drops a profile definition.
12.1 – Environment
You can use the DROP PROFILE statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in an SQL module or other compound
statement
o In dynamic SQL as a statement to be dynamically executed
12.2 – Format
DROP -+-> PROFILE --> <profilename> ----------------+--+-+---------------+-+->
+-> DEFAULT PROFILE -+----------------------+-+ | +-> CASCADE ----+ |
+-> ALIAS aliasname ---+ | +-> RESTRICT ---+ |
| +-> IF EXISTS --+ |
+-------<-----------+
12.3 – Arguments
12.3.1 – ALIAS aliasname
When attached to multiple databases, the aliasname is required to
direct the DROP command to the appropriate database.
12.3.2 – CASCADE
This option causes all user definitions to be altered to remove
the reference to this profile.
12.3.3 – DEFAULT_PROFILE
Drops the special profile RDB$DEFAULT_PROFILE.
12.3.4 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
12.3.5 – RESTRICT
If the profile is used by a user in the database, the DROP
PROFILE statement will fail. This is the default.
12.4 – Examples
Example 1: Using Delimited Identification Mixed-Case Profile
Names
SQL> DROP PROFILE Decision_Support;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-E-PRFNEXISTS, a quota does not exist with the name "DECISION_SUPPORT"
SQL> SET DIALECT 'SQL99';
SQL> DROP PROFILE "Decision_Support";
SQL> COMMIT;
Example 2: Using CASCADE to remove assigned profiles from users
This example demonstrates that there may be dependencies between
profiles and user objects. The CASCADE action will remove the
profile from all users to which is assigned.
SQL> create profile DECISION_SUPPORT
cont> comment is 'restrictions for read-only users'
cont> default transaction read only
cont> transaction modes (read only, shared);
SQL>
SQL> show profile DECISION_SUPPORT;
DECISION_SUPPORT
Comment: restrictions for read-only users
Transaction modes (read only, shared)
Default transaction read only
SQL>
SQL> create user FREEMAN
cont> identified externally
cont> profile DECISION_SUPPORT;
SQL>
SQL> show user FREEMAN;
FREEMAN
Identified externally
Account is unlocked
Profile: DECISION_SUPPORT
No roles have been granted to this user
SQL>
SQL> drop profile DECISION_SUPPORT restrict;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-E-PRFINUSE, entry "DECISION_SUPPORT" is referenced by user "FREEMAN"
SQL>
SQL> drop profile DECISION_SUPPORT cascade;
SQL>
SQL> show user FREEMAN;
FREEMAN
Identified externally
Account is unlocked
No roles have been granted to this user
SQL>
SQL> commit;
13 – ROLE
Drops a role previously created with the CREATE ROLE or GRANT
statement.
13.1 – Environment
You can use the DROP ROLE statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in an SQL module or other compound
statement
o In dynamic SQL as a statement to be dynamically executed
13.2 – Format
DROP ROLE ---> <role-name> -+-+-----------------+-+-->
| +---> CASCADE --+ |
| +---> RESTRICT --+ |
| +---> IF EXISTS --+ |
+------------<--------+
13.3 – Arguments
13.3.1 – CASCADE
Drops the specified role from the database and deletes all
references to this role that exist in other roles and access
control lists (ACLs).
13.3.2 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
13.3.3 – RESTRICT
Drops the specified role. If there are any references to this
role in another role or ACL, then the DROP ROLE statement fails.
The RESTRICT clause is the default.
13.3.4 – role-name
An existing role-name in the database (such as one created
with the CREATE ROLE statement). You cannot specify one of the
predefined roles.
13.4 – Examples
Example 1: Dropping a Role from the Database
SQL> SHOW ROLES;
Roles in database with filename mf_personnel.rdb
DOCUMENTATION
SQL> DROP ROLE DOCUMENTATION RESTRICT;
SQL> SHOW ROLES;
Roles in database with filename mf_personnel.rdb
No Roles Found
14 – Routine
Deletes a routine definition, external or stored, from an Oracle
Rdb database. External routine refers to both external functions
and external procedures. Stored routine refers to both stored
functions and stored procedures.
14.1 – Environment
You can use the DROP FUNCTION and DROP PROCEDURE statements:
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
14.2 – Format
DROP -+-> FUNCTION --+-> <routine-name> -+-+---------------+-+->
+-> PROCEDURE -+ | +-> RESTRICT ---+ |
| +-> CASCADE ----+ |
| +-> RESRICT ----+ |
| +-> IF EXISTS --+ |
+----------<--------+
14.3 – Arguments
14.3.1 – CASCADE
Deletes the routine definition even when there are dependencies
on the specified routine. Any referencing routines are marked
invalid.
14.3.2 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
14.3.3 – RESTRICT
Prevents the removal of an external or stored routine definition
when the routine is referenced by any other object within an
Oracle Rdb database.
RESTRICT is the default.
14.3.4 – routine-name
Identifies the name of the external or stored routine definition
in the Oracle Rdb database.
14.4 – Examples
Example 1: Deleting an external function definition from an
Oracle Rdb database
If you want to alter an external function definition, you must
first delete it and then create it again with the changes you
plan. This example shows how to delete the COSINE_F function.
SQL> DROP FUNCTION cosine_f RESTRICT;
Example 2: Deleting a routine from a stored module
The DROP FUNCTION and DROP PROCEDURE statements can be used to
drop routines from a stored module. If the routine is referenced
by other objects then the CASCADE option may be required to
successfully drop the routine.
See also the DROP FUNCTION and DROP PROCEDURE clauses of ALTER
MODULE which can be used to perform the same task.
This example removes a function from the stored module TIME_
ROUTINES that is no longer in use.
SQL> set dialect 'sql99';
SQL> create database filename junk;
SQL>
SQL> create module TIME_ROUTINES
cont>
cont> function GET_TIME ()
cont> returns TIME (2);
cont> return CURRENT_TIME (2);
cont>
cont> function DAY_OF_WEEK (in :dt date)
cont> returns VARCHAR(10);
cont> return case EXTRACT (weekday from :dt)
cont> when 1 then 'Monday'
cont> when 2 then 'Tuesday'
cont> when 3 then 'Wednesday'
cont> when 4 then 'Thursday'
cont> when 5 then 'Friday'
cont> when 6 then 'Saturday'
cont> when 7 then 'Sunday'
cont> else '***'
cont> end;
cont>
cont> end module;
SQL>
SQL> show module TIME_ROUTINES;
Information for module TIME_ROUTINES
Header:
TIME_ROUTINES
No description found
Module ID is: 1
Routines in module TIME_ROUTINES:
DAY_OF_WEEK
GET_TIME
SQL> drop function GET_TIME cascade;
SQL> show module TIME_ROUTINES;
Information for module TIME_ROUTINES
Header:
TIME_ROUTINES
No description found
Module ID is: 1
Routines in module TIME_ROUTINES:
DAY_OF_WEEK
SQL>
15 – SCHEMA
Deletes a schema and all the definitions that it contains.
15.1 – Environment
You can use the DROP SCHEMA 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
15.2 – Format
DROP SCHEMA <schema-name> -+-+---------------+-+-->
| +-> CASCADE ---+ |
| +-> RESTRICT --+ |
| +-> IF EXISTS --+ |
+----------<--------+
15.3 – Arguments
15.3.1 – CASCADE
Deletes all other definitions (views, constraints, tables,
sequences, indexes, and triggers) that refer to the named schema
and then deletes that schema definition. This is known as a
cascading delete.
If you specify the CASCADE keyword, SQL deletes all definitions
contained by the schema before deleting the schema.
If you do not specify the CASCADE keyword, the schema must be
empty.
15.3.2 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
15.3.3 – RESTRICT
Returns an error message if other definitions refer to the named
schema. The DROP SCHEMA RESTRICT statement will not delete a
schema until you have deleted all other definitions that refer to
the named schema. The DROP SCHEMA statement specifies an implicit
RESTRICT by default.
15.3.4 – schema-name
Specifies the schema name. You must qualify the schema name with
catalog and alias names if the schema is not in the default
catalog and database. For more information about schema names,
see the User_Supplied_Names HELP topic.
15.4 – Examples
Example 1: Deleting a schema with implicit RESTRICT
In the following example, the user must delete the definitions
that refer to the schema RECRUITING before deleting the schema
itself.
After setting the default schema to RECRUITING and the default
catalog to ADMINISTRATION, the user can qualify each definition
name with only the alias CORP.
SQL> ATTACH 'ALIAS CORP FILENAME CORPORATE_DATA';
SQL> SET CATALOG '"CORP.ADMINISTRATION"';
SQL> SET SCHEMA '"CORP.ADMINISTRATION".RECRUITING';
SQL> SET QUOTING RULES 'SQL92';
SQL> DROP SCHEMA "CORP.RECRUITING";
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-E-SCHEMAINUSE, schema RECRUITING currently in use
SQL> DROP TABLE "CORP.CANDIDATES";
SQL> DROP TABLE "CORP.COLLEGES";
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-CONEXI, relation COLLEGES is referenced in constraint DEGREES_FOREIGN3
-RDMS-F-RELNOTDEL, relation COLLEGES has not been deleted
SQL> DROP TABLE "CORP.DEGREES";
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-TRGEXI, relation DEGREES is referenced in trigger
EMPLOYEE_ID_CASCADE_DELETE
SQL> DROP TABLE "CORP.RESUMES";
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-TRGEXI, relation RESUMES is referenced in trigger
EMPLOYEE_ID_CASCADE_DELETE
-RDMS-F-RELNOTDEL, relation RESUMES has not been deleted
SQL> --
SQL> -- The trigger is part of another schema, PERSONNEL. Since this
SQL> -- is not the default schema, the user qualifies the trigger name
SQL> -- with schema and names.
SQL> --
SQL> DROP TRIGGER "CORP.ADMINSTRATION".PERSONNEL.EMPLOYEE_ID_CASCADE_DELETE;
SQL> DROP CONSTRAINT "CORP.DEGREES_FOREIGN3";
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-CONDELVIAREL, constraint DEGREES_FOREIGN3 can only be deleted by
changing or deleting relation DEGREES
SQL> DROP TABLE "CORP.DEGREES";
SQL> DROP TABLE "CORP.RESUMES";
SQL> DROP TABLE "CORP.COLLEGES";
SQL> DROP SCHEMA "CORP.RECRUITING";
Example 2: Deleting a schema with CASCADE
In the following example, SQL deletes the definitions that refer
to the schema ACCOUNTING, then deletes the schema itself:
SQL> DROP SCHEMA "CORP.ACCOUNTING" CASCADE;
Domain "CORP.ADMINISTRATION".ACCOUNTING.BUDGET is also being dropped.
Domain "CORP.ADMINISTRATION".ACCOUNTING.CODE is also being dropped.
SQL>
16 – SEQUENCE
Drops a specified sequence.
16.1 – Environment
You can use the DROP SEQUENCE statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in an SQL module or other compound
statement
o In dynamic SQL as a statement to be dynamically executed
16.2 – Format
DROP SEQUENCE <sequence-name> --+-+---------------+-+-->
| +--> CASCADE --+ |
| +--> RESTRICT --+ |
| +--> IF EXISTS -+ |
+-----------<-------+
16.3 – Arguments
16.3.1 – CASCADE
The CASCADE clause specifies that you want SQL to invalidate
all objects that refer to the sequence and then delete the
sequence definition. If you delete a sequence referenced by a
stored routine or trigger with a routine or language-semantic
dependency, SQL also marks the affected stored routine or trigger
as invalid.
16.3.2 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
16.3.3 – RESTRICT
The RESTRICT clause prevents the removal of a sequence definition
(the DROP SEQUENCE statement fails) when the sequence is
referenced by any other object within the Oracle Rdb database.
The RESTRICT clause is the default.
16.3.4 – sequence-name
An existing sequence name in the database. To specify lowercase
characters or characters not in the SQL repertoire, enclose the
sequence name in single quotation marks (').
16.4 – Examples
Example 1: Dropping a Sequence
SQL> SHOW SEQUENCE;
Sequences in database with filename mf_personnel.rdb
EMPID
SQL> DROP SEQUENCE EMPID;
SQL> SHOW SEQUENCE;
Sequences in database with filename mf_personnel.rdb
No Sequences Found
SQL>
17 – STORAGE_MAP
Deletes the specified storage map definition.
17.1 – Environment
You can use the DROP STORAGE MAP 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
17.2 – Format
DROP STORAGE MAP --> <map-name> --+--------------+->
+-> IF EXISTS -+
17.3 – Arguments
17.3.1 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
17.3.2 – map-name
Specifies the name of the storage map you want to delete.
17.4 – Examples
Example 1: Deleting a storage map in interactive SQL
This example deletes a storage map. You cannot delete a storage
map that refers to a table that contains data.
SQL> ATTACH 'FILENAME mf_personnel';
SQL> DROP STORAGE MAP WORK_STATUS_MAP;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-RELNOTEMPTY, relation WORK_STATUS has data in it
SQL> DELETE FROM WORK_STATUS;
3 rows deleted
SQL> DROP STORAGE MAP WORK_STATUS_MAP;
SQL>
18 – SYNONYM
Drops a synonym definition.
18.1 – Environment
You can use the DROP SYNONYM statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in an SQL module or other compound
statement
o In dynamic SQL as a statement to be dynamically executed
18.2 – Format
DROP -+-----------+-> SYNONYM <synonym-name> -+-+---------------+-+->
+-> PUBLIC -+ | +-> CASCADE ---+ |
| +-> RESTRICT ---+ |
| +-> IF EXISTS --+ |
+---------<---------+
18.3 – Arguments
18.3.1 – CASCADE
Specifies that you want SQL to delete the synonym definition even
if other database objects reference this name. This might later
cause errors when executing queries. Stored functions, stored
procedures, and triggers that reference this name will be marked
as invalid.
18.3.2 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
18.3.3 – PUBLIC
This optional clause is provided for compatibility with the
Oracle database server. It is currently not used by Oracle
Rdb. Its presence or absence may be used by future releases.
Oracle Corporation recommends you use the PUBLIC keyword in
applications.
18.3.4 – RESTRICT
Specifies that you want SQL to abort the DROP statement if it
detects any database object referencing this name. This is the
default.
18.3.5 – synonym-name
The name of an existing synonym you want to drop.
18.4 – Example
Example 1: Dropping a Synonym
SQL> DROP PUBLIC SYNONYM employees CASCADE;
19 – TABLE
Deletes the specified table definition.
If you use the PATHNAME qualifier when you attach to the
database, the DROP TABLE statement also deletes the table
definition from the repository.
19.1 – Environment
You can use the DROP TABLE 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
19.2 – Format
DROP TABLE <table-name> -+-+----------------+-+->
| +-> CASCADE ---+ |
| +-> RESTRICT ---+ |
| +-> IF EXISTS ---+ |
+----------<---------+
19.3 – Arguments
19.3.1 – CASCADE
Specifies that you want SQL to delete all other definitions
(constraints, indexes, modules, storage maps, triggers, and
views) that refer to the named table and then delete that
table definition. This is known as a cascading delete. For
stored routines or triggers with a routine or language-semantic
dependency, SQL also marks the affected routines and triggers as
invalid.
19.3.2 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
19.3.3 – RESTRICT
Specifies that you want SQL to delete only the named table
definition. If constraints, modules, triggers, or views are
defined that refer to the named table, SQL does not delete the
table. If there are indexes or storage maps that refer to the
named table, SQL deletes the table and storage map and does not
issue an error.
19.3.4 – table-name
Specifies the name of the table definition you want to delete.
19.4 – Examples
Example 1: Deleting a table from an attached database
SQL> ATTACH 'ALIAS PERS FILENAME personnel';
SQL> DROP TABLE PERS.DEGREES;
SQL> COMMIT;
Example 2: Deleting a table and definitions that reference it
from the default database
SQL> ATTACH 'FILENAME corporate_data';
SQL> DROP TABLE ADMINISTRATION.PERSONNEL.EMPLOYEES CASCADE;
View ADMINISTRATION.PERSONNEL.REVIEW_DATE is also being dropped.
View ADMINISTRATION.PERSONNEL.CURRENT_INFO is also being dropped.
View ADMINISTRATION.PERSONNEL.CURRENT_SALARY is also being dropped.
View ADMINISTRATION.PERSONNEL.CURRENT_JOB is also being dropped.
Constraint ADMINISTRATION.RECRUITING.DEGREES_FOREIGN2 is also being dropped.
Constraint ADMINISTRATION.PERSONNEL.EMPLOYEES_PRIMARY_EMPLOYEE_ID is also
being dropped.
Constraint ADMINISTRATION.PERSONNEL.EMP_SEX_VALUES is also being dropped.
Constraint ADMINISTRATION.PERSONNEL.HOURLY_HISTORY_FOREIGN1 is also being
dropped.
Constraint ADMINISTRATION.PERSONNEL.JOB_HISTORY_FOREIGN1 is also being
dropped.
Constraint ADMINISTRATION.RECRUITING.RESUMES_FOREIGN2 is also being dropped.
Constraint ADMINISTRATION.PERSONNEL.SALARY_HISTORY_FOREIGN1 is also being
dropped.
Constraint ADMINISTRATION.PERSONNEL.STATUS_CODE_VALUES is also being dropped.
Index ADMINISTRATION.PERSONNEL.EMP_LAST_NAME is also being dropped.
Index ADMINISTRATION.PERSONNEL.EMP_EMPLOYEE_ID is also being dropped.
Trigger ADMINISTRATION.PERSONNEL.EMPLOYEE_ID_CASCADE_DELETE is also being
dropped.
Trigger ADMINISTRATION.PERSONNEL.STATUS_CODE_CASCADE_UPDATE is also being
dropped.
20 – TRIGGER
Deletes a trigger definition from the physical database and, if
the database was attached with PATHNAME, from the repository.
20.1 – Environment
You can use the DROP TRIGGER 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
20.2 – Format
DROP TRIGGER ---> <trigger-name> --+---------------+->
+--> IF EXISTS -+
20.3 – Arguments
20.3.1 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
20.3.2 – trigger-name
Specifies the name of the trigger to be deleted.
20.4 – Examples
Example 1: Deleting the EMPLOYEE_ID_CASCADE_DELETE trigger
SQL> ATTACH 'FILENAME personnel';
SQL> SHOW TRIGGERS
User triggers in database with filename PERSONNEL
COLLEGE_CODE_CASCADE_UPDATE
EMPLOYEE_ID_CASCADE_DELETE
STATUS_CODE_CASCADE_UPDATE
SQL> DROP TRIGGER EMPLOYEE_ID_CASCADE_DELETE;
SQL> SHOW TRIGGERS
User trigggers in database with filename PERSONNEL
COLLEGE_CODE_CASCADE_UPDATE
STATUS_CODE_CASCADE_UPDATE
SQL>
21 – USER
Removes the entry (such as one created with the CREATE USER or
GRANT statement) for a user name or special user class from the
database.
21.1 – Environment
You can use the DROP statement:
o In interactive SQL
o Embedded in host language programs
o As part of a procedure in an SQL module or other compound
statement
o In dynamic SQL as a statement to be dynamically executed
21.2 – Format
DROP USER -----> <username> --+-+---------------+-+->
| +-> CASCADE --+ |
| +-> RESTRICT --+ |
| +-> IF EXISTS --+ |
+----------<--------+
21.3 – Arguments
21.3.1 – CASCADE
The CASCADE clause drops the specified user from the database
and deletes all references to this user that exist in the access
control lists (ACLs), modules, and schemas. If the PUBLIC user is
dropped, ACLs are not processed to remove the PUBLIC entry.
21.3.2 – RESTRICT
The RESTRICT clause drops the specified user. If there are
any references to this user in another ACL, then the DROP USER
statement fails.
The RESTRICT clause is the default.
21.3.3 – username
An existing user name in the database.
21.4 – Example
Example 1: Dropping a User
SQL> SHOW USER
Users in database with filename mf_personnel.rdb
JSMITH
NSTUART
SQL> DROP USER JSMITH;
SQL> SHOW USER
Users in database with filename mf_personnel.rdb
NSTUART
SQL>
22 – VIEW
Deletes the specified view definition. When the DROP VIEW
statement executes, SQL deletes the view definition from the
database. If you attach to the database using the PATHNAME
qualifier, SQL also deletes the view definition from the
repository.
22.1 – Environment
You can use the DROP VIEW 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
22.2 – Format
DROP VIEW <view-name> --+-+---------------+-+->
| +-> CASCADE ---+ |
| +-> RESTRICT --+ |
| +-> IF EXISTS --+ |
+----------<--------+
22.3 – Arguments
22.3.1 – CASCADE
Specifies that you want SQL to delete all other view definitions
that refer to the named view and then delete that view
definition. This is known as a cascading delete. If you delete
a view referenced by a stored routine or trigger with a routine
or language-semantic dependency, SQL also marks the affected
routines and triggers as invalid.
22.3.2 – IF_EXISTS
Prevents SQL command language from displaying error messages if
the referenced object does not exist in the database.
22.3.3 – RESTRICT
Specifies that you want SQL to delete only the named view
definition. If there are other views, triggers, or routines that
refer to the named view, the deletion fails. RESTRICT is the
default.
22.3.4 – view-name
Specifies the name of the view definition you want to delete.
22.4 – Examples
Example 1: Deleting a view definition
The following example deletes the view definition CURRENT_INFO:
SQL> DROP VIEW CURRENT_INFO;
SQL> COMMIT;
Example 2: Deleting a view with dependent views
This example shows that SQL will not automatically delete any
views that refer to the view named in the DROP VIEW statement.
You must use the CASCADE keyword to delete a view with dependent
views.
SQL> DROP VIEW CURRENT_JOB;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-VIEWINVIEW, view CURRENT_JOB is referenced by view CURRENT_INFO
-RDMS-F-VIEWNOTDEL, view CURRENT_JOB has not been deleted
SQL> DROP VIEW CURRENT_JOB CASCADE;
View CURRENT_INFO is also being dropped.
SQL> COMMIT;
Example 3: Adding new definitions to a database
When updating metadata definitions using a predefined SQL script
it sometimes required to remove objects that may not be present
in all databases being maintained. Adding a DROP VIEW, for
instance, will result in an error as shown here.
SQL> drop view CURRENT_INFO;
%SQL-F-RELNOTDEF, Table CURRENT_INFO is not defined in database or schema
SQL> create view CURRENT_INFO
cont> ...etc...
By using the IF EXISTS clause the error message is supressed and
makes for a less confusing execution of the maintance script.
SQL> drop view CURRENT_INFO if exists;
SQL> create view CURRENT_INFO
cont> ...etc...