Delete, Remove or Drop Oracle Stored Packages with Drop Package

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.

Related Articles

• How Drop Tablespace and Recover Oracle Database When Accidentally Delete Datafile
• How to Remove and Drop Datafiles from Tablespace in Oracle Database
• ORA-02449 Oracle Drop Table Error
• ORA-00942 Table or View Does Not Exist Oracle Error
• How to Delete Existing WordPress Post Revisions Stored/Saved
• Oracle Database Import Error 3113/3114
• Oracle EXP-00091 Error When Export Database
• Oracle Database Link
• Check Oracle Version
• Create, Add or Split Oracle Database Partition Fails with ORA-14080 Error

This entry was posted on Thursday, January 11th, 2007 at 6:52 pm and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.