Oracle Package is an object in Oracle database that allows developers and designers to group procedures and functions into common groups, typically based upon their functionality. Package can be treated as the container for functions and procedures. A package consists of two sections which is the package specification (export script file will be in .pks extension) which must be declared first and contains a definition of any objects that can be referenced from outside of the package. The other section is a package body (export SQL script file will be in .pkb extension) that contains the implementation of the objects or stored procedures within the package. With package, a procedure can be hidden inside the package by not declaring them in the package specification.

At same time you may no longer need a package, and wish to remove, delete, or drop the package from the Oracle database. To do so, simply use the DROP PACKAGE SQL command statement from SQL*Plus or Toad or other database too to drop a stored package.

Syntax of the Drop Package

DROP PACKAGE [BODY] [schema.]package_name;

The optional BODY parameter is specified when you want to drop only the body of the package. If this option is omitted, Oracle drops both the body and specification of the package. If the package to drop is in your own schema, then there is no need to specify the scheme containing the package too, else you will need to put in the appropriate schema name. Beside, you must have the DROP ANY PROCEDURE system privilege too.

Note: Do not use this statement to remove a single object from a package. Instead, re-create the package without the object using the CREATE PACKAGE and CREATE PACKAGE BODY SQL statements, or by using the SQL command with the OR REPLACE clause.

You May Also Interested In: