Overview

This topic discusses the DDL operations on PL stored units, including create, alter, and drop operations.

CREATE [OR REPLACE]

The CREATE [OR REPLACE] clause is used to create a PL/SQL unit.

The following SQL statements can be used: CREATE FUNCTION, CREATE PACKAGE, CREATE PACKAGE BODY, CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE, and CREATE TYPE BODY. The CREATE statements allow the use of the optional OR REPLACE clause. When you specify OR REPLACE, it allows you to recreate the PL unit based on existing PL units, changing the declaration or definition without deleting them, and regaining the object privileges that were previously granted to the PL unit. If the PL unit is redefined, the database will recompile it.

ALTER

The ALTER statement recompiles a PL unit without recreation. The following SQL statements can be used: ALTER FUNCTION, ALTER PACKAGE, ALTER PROCEDURE, ALTER TRIGGER, and ALTER TYPE. You can use the ALTER statement for the following reasons:

• Explicitly recompiles invalidated stored units, avoiding implicit recompiles at runtime and the associated performance overhead and compilation errors.

• Recompile the stored unit using a different set of compiler options.

• Enable or disable triggers.

DROP

DROP deletes an existing PL unit.

The following SQL statements can be used: DROP FUNCTION, DROP PACKAGE, DROP PROCEDURE, DROP TRIGGER, DROP TYPE, and DROP TYPE BODY.