Oracle Database Tips by Donald Burleson

Alteration of the Package 

The DBA will be required to alter a package when there are changes to tables, views, sequences, and so on that the package procedures and functions reference. This is accomplished through the use of the CREATE OR REPLACE PACKAGE [BODY] form of the CREATE PACKAGE command. The format for the command is identical to that of the CREATE PACKAGE [BODY] command. But be aware that all procedures, variables, and functions referenced in the CREATE PACKAGE command must be present in the CREATE OR REPLACE PACKAGE BODY command, for if you just use the command with a single procedure or function you want altered, that will be the only object left in the body when you are finished. Perhaps with a future release we will be able to use the package definition as a link list, and this won't be required. There is also an ALTER PACKAGE BODY command that is used only to recompile the package body. The format of the ALTER command follows.

See Code Depot   

The DEBUG clause has been added to compile and store debugging information for the specified area of the package for the PL/SQL debugger.

Dropping a Package

Even such wonderful things as packages have limited lifetimes. Applications are replaced or are no longer needed; entire database practices are rethought and changed. This leads to the requirement to be able to drop packages that are no longer needed. This is accomplished through the DROP PACKAGE command. The format of this command follows:

See Code Depot

Exclusion of the keyword BODY results in the drop of both the definition and the body. Inclusion of BODY drops just the package body, leaving the definition intact.

When a package is dropped, all dependent objects are invalidated. If the package is not re-created before one of the dependent objects is accessed, Oracle tries to recompile the package; this will return an error and cause failure of the command.

www.dba-oracle.com/oracle_scripts.htm