1  DROP
 

2  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.
 

3  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
 

3  Format

 DROP CATALOG <catalog-name> -+-+---------------+-+->
                              | +-> CASCADE ----+ |
                              | +-> RESTRICT  --+ |
                              | +-> IF EXISTS --+ |
                              +----------<--------+



 catalog-name =
 
 -+------> <name-of-catalog> --------------+->
  |                                        |
  +-> " -> <alias>.<name-of-catalog> ->"  -+
 

 

3  Arguments
 

4  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.
 

4  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.
 

4  catalog-name

   Specifies the module catalog name.
 

4  IF_EXISTS

   Prevents SQL command language from displaying error messages if
   the referenced object does not exist in the database.
 

3  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.
 

3  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
 

3  Format

 DROP COLLATING SEQUENCE --> <collation-name> -+---------------+->
                                               +-> IF EXISTS --+

 

3  Arguments
 

4  collation-name

   Specifies the collation-name argument you used when creating the
   collating sequence in the CREATE COLLATING SEQUENCE statement.
 

4  IF_EXISTS

   Prevents SQL command language from displaying error messages if
   the referenced object does not exist in the database.
 

3  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
 

2  CONSTRAINT
   Deletes the named constraints.
 

3  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  Format

 DROP CONSTRAINT --> <constraint-name> -+---------------+->
                                        +-> IF EXISTS --+

 

3  Arguments
 

4  constraint-name

   Specifies the name of the constraint that you want to delete.
 

4  IF_EXISTS

   Prevents SQL command language from displaying error messages if
   the referenced object does not exist in the database.
 

3  Example
   Example 1: Deleting a constraint

   The following statement deletes the SEX_NOT_NULL constraint.

   SQL> DROP CONSTRAINT SEX_NOT_NULL;
 

2  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.
 

3  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
 

3  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>'  -+
 

 

3  Arguments
 

4  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  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  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  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  USER_username

   Defines a character string literal that specifies the operating
   system user name that the database system uses for privilege
   checking.
 

4  USING_password

   Defines a character string literal that specifies the user's
   password for the user name specified in the USER clause.
 

3  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;
 

2  DOMAIN
   Deletes a domain definition. If you attached to the database
   using the PATHNAME qualifier, SQL also deletes the domain
   definition from the repository.
 

3  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
 

3  Format

 DROP DOMAIN --> <domain-name> --+---------------+-->
                                 +-> IF EXISTS --+
 

 

3  Arguments
 

4  domain-name

   Specifies the name of the domain you want to delete.
 

4  IF_EXISTS

   Prevents SQL command language from displaying error messages if
   the referenced object does not exist in the database.
 

3  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>
 

2  FUNCTION
   For information on deleting an external or stored function
   definition, see the DROP Routine Help topic.
 

2  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.
 

3  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
 

3  Format

 DROP INDEX ---> <index-name> --+-+---------------+-+->
                                | +-> RESTRICT  --+ |
                                | +-> CASCADE   --+ |
                                | +-> IF EXISTS --+ |
                                +----------<--------+

 

3  Arguments
 

4  CASCADE

   Specifies that you want SQL to modify any storage map that uses
   this index to be a NO PLACEMENT VIA INDEX storage map.
 

4  IF_EXISTS

   Prevents SQL command language from displaying error messages if
   the referenced object does not exist in the database.
 

4  index-name

   Specifies the name of the index definition you want to delete.
 

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.
 

3  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;
 

2  MODULE
   Deletes a module from an Oracle Rdb database.
 

3  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
 

3  Format

 DROP MODULE --> <module-name> -+-+---------------+-+->
                                | +-> RESTRICT  --+ |
                                | +-> CASCADE   --+ |
                                | +-> IF EXISTS --+ |
                                +----------<--------+

 

3  Arguments
 

4  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.
 

4  IF_EXISTS

   Prevents SQL command language from displaying error messages if
   the referenced object does not exist in the database.
 

4  module-name

   Identifies the name of the module.
 

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.
 

3  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;
 

2  OUTLINE
   Deletes a query outline.
 

3  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.
 

3  Format

 DROP OUTLINE ---> <outline-name> -+---------------+->
                                   +-> IF EXISTS --+

 

3  Arguments
 

4  IF_EXISTS

   Prevents SQL command language from displaying error messages if
   the referenced object does not exist in the database.
 

4  outline-name

   Specifies the name of the outline you want to delete.
 

3  Examples
   Example 1. Deleting an outline

   SQL> DROP OUTLINE MY_OUTLINE;
 

2  PATHNAME
   Deletes the repository definitions. It does not delete the
   physical database files.
 

3  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
 

3  Format

 DROP PATHNAME ---> <path-name> --->

 

3  Arguments
 

4  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.
 

3  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";
 

2  PROCEDURE
   For information on deleting an external or stored procedure
   definition, see the DROP Routine Help topic.
 

2  PROFILE
   Drops a profile definition.
 

3  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
 

3  Format

 DROP -+-> PROFILE --> <profilename> ----------------+--+-+---------------+-+->
       +-> DEFAULT PROFILE -+----------------------+-+  | +-> CASCADE ----+ |
                            +-> ALIAS aliasname ---+    | +-> RESTRICT ---+ |
                                                        | +-> IF EXISTS --+ |
                                                        +-------<-----------+

 

3  Arguments
 

4  ALIAS_aliasname

   When attached to multiple databases, the aliasname is required to
   direct the DROP command to the appropriate database.
 

4  CASCADE

   This option causes all user definitions to be altered to remove
   the reference to this profile.
 

4  DEFAULT_PROFILE

   Drops the special profile RDB$DEFAULT_PROFILE.
 

4  IF_EXISTS

   Prevents SQL command language from displaying error messages if
   the referenced object does not exist in the database.
 

4  RESTRICT

   If the profile is used by a user in the database, the DROP
   PROFILE statement will fail. This is the default.
 

3  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;
 

2  ROLE
   Drops a role previously created with the CREATE ROLE or GRANT
   statement.
 

3  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
 

3  Format

 DROP ROLE ---> <role-name> -+-+-----------------+-+-->
                             | +---> CASCADE   --+ |
                             | +---> RESTRICT  --+ |
                             | +---> IF EXISTS --+ |
                             +------------<--------+

 

3  Arguments
 

4  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).
 

4  IF_EXISTS

   Prevents SQL command language from displaying error messages if
   the referenced object does not exist in the database.
 

4  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.
 

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.
 

3  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
 

2  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.
 

3  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
 

3  Format

 DROP -+-> FUNCTION --+-> <routine-name> -+-+---------------+-+->
       +-> PROCEDURE -+                   | +-> RESTRICT ---+ |
                                          | +-> CASCADE ----+ |
                                          | +-> RESRICT ----+ |
                                          | +-> IF EXISTS --+ |
                                          +----------<--------+

 

3  Arguments
 

4  CASCADE

   Deletes the routine definition even when there are dependencies
   on the specified routine. Any referencing routines are marked
   invalid.
 

4  IF_EXISTS

   Prevents SQL command language from displaying error messages if
   the referenced object does not exist in the database.
 

4  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.
 

4  routine-name

   Identifies the name of the external or stored routine definition
   in the Oracle Rdb database.
 

3  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>
 

2  SCHEMA
   Deletes a schema and all the definitions that it contains.
 

3  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
 

3  Format

 DROP SCHEMA <schema-name> -+-+---------------+-+-->
                            | +-> CASCADE  ---+ |
                            | +-> RESTRICT  --+ |
                            | +-> IF EXISTS --+ |
                            +----------<--------+

 

3  Arguments
 

4  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.
 

4  IF_EXISTS

   Prevents SQL command language from displaying error messages if
   the referenced object does not exist in the database.
 

4  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.
 

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.
 

3  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>
 

2  SEQUENCE
   Drops a specified sequence.
 

3  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
 

3  Format

 DROP SEQUENCE <sequence-name> --+-+---------------+-+-->
                                 | +--> CASCADE  --+ |
                                 | +--> RESTRICT --+ |
                                 | +--> IF EXISTS -+ |
                                 +-----------<-------+

 

3  Arguments
 

4  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.
 

4  IF_EXISTS

   Prevents SQL command language from displaying error messages if
   the referenced object does not exist in the database.
 

4  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.
 

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 (').
 

3  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>
 

2  STORAGE_MAP
   Deletes the specified storage map definition.
 

3  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
 

3  Format

 DROP STORAGE MAP --> <map-name> --+--------------+->
                                   +-> IF EXISTS -+
 
 

 

3  Arguments
 

4  IF_EXISTS

   Prevents SQL command language from displaying error messages if
   the referenced object does not exist in the database.
 

4  map-name

   Specifies the name of the storage map you want to delete.
 

3  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>
 

2  SYNONYM
   Drops a synonym definition.
 

3  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
 

3  Format

 DROP -+-----------+-> SYNONYM <synonym-name> -+-+---------------+-+->
       +-> PUBLIC -+                           | +-> CASCADE  ---+ |
                                               | +-> RESTRICT ---+ |
                                               | +-> IF EXISTS --+ |
                                               +---------<---------+

 

3  Arguments
 

4  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.
 

4  IF_EXISTS

   Prevents SQL command language from displaying error messages if
   the referenced object does not exist in the database.
 

4  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.
 

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.
 

4  synonym-name

   The name of an existing synonym you want to drop.
 

3  Example

   Example 1: Dropping a Synonym

   SQL> DROP PUBLIC SYNONYM employees CASCADE;
 

2  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.
 

3  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
 

3  Format

 DROP TABLE <table-name> -+-+----------------+-+->
                          | +-> CASCADE   ---+ |
                          | +-> RESTRICT  ---+ |
                          | +-> IF EXISTS ---+ |
                          +----------<---------+

 

3  Arguments
 

4  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.
 

4  IF_EXISTS

   Prevents SQL command language from displaying error messages if
   the referenced object does not exist in the database.
 

4  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.
 

4  table-name

   Specifies the name of the table definition you want to delete.
 

3  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.
 

2  TRIGGER
   Deletes a trigger definition from the physical database and, if
   the database was attached with PATHNAME, from the repository.
 

3  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
 

3  Format

 DROP TRIGGER ---> <trigger-name> --+---------------+->
                                    +--> IF EXISTS -+

 

3  Arguments
 

4  IF_EXISTS

   Prevents SQL command language from displaying error messages if
   the referenced object does not exist in the database.
 

4  trigger-name

   Specifies the name of the trigger to be deleted.
 

3  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>
 

2  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.
 

3  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
 

3  Format

 DROP USER -----> <username> --+-+---------------+-+->
                               | +-> CASCADE   --+ |
                               | +-> RESTRICT  --+ |
                               | +-> IF EXISTS --+ |
                               +----------<--------+

 

3  Arguments
 

4  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.
 

4  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.
 

4  username

   An existing user name in the database.
 

3  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>
 

2  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.
 

3  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
 

3  Format

 DROP VIEW <view-name> --+-+---------------+-+->
                         | +-> CASCADE  ---+ |
                         | +-> RESTRICT  --+ |
                         | +-> IF EXISTS --+ |
                         +----------<--------+

 

3  Arguments
 

4  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.
 

4  IF_EXISTS

   Prevents SQL command language from displaying error messages if
   the referenced object does not exist in the database.
 

4  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.
 

4  view-name

   Specifies the name of the view definition you want to delete.
 

3  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...