Oracle7 Server SQL Reference

DROP PROCEDURE

Purpose

To remove a stand-alone stored procedure from the database.

Prerequisites

The procedure must be in your own schema or you must have DROP ANY PROCEDURE system privilege.

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the cluster's creation label or you must satisfy one of the following criteria:

If the procedure's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges

If the procedure's creation label is lower than your DBMS label, you must have WRITEDOWN system privilege.

If the procedure's creation label and your DBMS label are not comparable, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

Syntax

Keywords and Parameters

schema

is the schema containing the procedure. If you omit schema, Oracle7 assumes the procedure is in your own schema.

procedure

is the name of the procedure to be dropped.

Usage Notes

When you drop a procedure, Oracle7 invalidates any local objects that depend upon the dropped procedure. If you subsequently reference one of these objects, Oracle7 tries to recompile the object and returns an error message if you have not recreated the dropped procedure.

For information on how Oracle7 maintains dependencies among schema objects, including remote objects, see the "Dependencies Among Schema Objects" chapter of Oracle7 Server Concepts.

You can only use this command to drop a stand-alone procedure. To remove a procedure that is part of a package, use one of the following methods:

Drop the entire package using the DROP PACKAGE command.

Redefine the package without the procedure using the CREATE PACKAGE command with the OR REPLACE option.

Example

The following statement drops the procedure TRANSFER owned by the user KERNER:

DROP PROCEDURE kerner.transfer

When you drop the TRANSFER procedure, Oracle7 invalidates all objects that depend upon TRANSFER.