This chapter explains how to use the SQL statements that create, change, and drop stored PL/SQL units.

Each of these SQL statements creates a PL/SQL unit at schema level and stores it in the database:

• CREATE FUNCTION Statement

• CREATE LIBRARY Statement

• CREATE PACKAGE Statement

• CREATE PACKAGE BODY Statement

• CREATE PROCEDURE Statement

• CREATE TRIGGER Statement

• CREATE TYPE Statement

• CREATE TYPE BODY Statement

Each of these CREATE statements has an optional OR REPLACE clause. Specify OR REPLACE to re-create an existing PL/SQL unit—that is, to change its declaration or definition without dropping it, re-creating it, and regranting object privileges previously granted on it. If you redefine a PL/SQL unit, the database recompiles it.

None of these CREATE statements can appear in a PL/SQL block.

To recompile an existing PL/SQL unit without re-creating it (without changing its declaration or definition), use one of these SQL statements:

• ALTER FUNCTION Statement

• ALTER LIBRARY Statement

• ALTER PACKAGE Statement

• ALTER PROCEDURE Statement

• ALTER TRIGGER Statement

• ALTER TYPE Statement

Reasons to use an ALTER statement are:

• To explicitly recompile a stored unit that has become invalid, thus eliminating the need for implicit runtime recompilation and preventing associated runtime compilation errors and performance overhead.

• To recompile a stored unit with different compilation parameters.

• To enable or disable a trigger.

• To specify the EDITIONABLE or NONEDITIONABLE property of a stored unit whose schema object type is not yet editionable in its schema.

The ALTER TYPE statement has additional uses.

To drop an existing PL/SQL unit from the database, use one of these SQL statements:

• DROP FUNCTION Statement

• DROP LIBRARY Statement

• DROP PACKAGE Statement

• DROP PROCEDURE Statement

• DROP TRIGGER Statement

• DROP TYPE Statement

• DROP TYPE BODY Statement