Overview

This chapter introduces DDL operations for PL stored units, including creation, modification, and deletion.

CREATE [OR REPLACE]

CREATE [OR REPLACE] is used to create PL units.

SQL statements supported include CREATE FUNCTION, CREATE PACKAGE, CREATE PACKAGE BODY, CREATE PROCEDURE, CREATE TRIGGER, the CREATE TYPE statement, and CREATE TYPE BODY.

Every CREATE statement contains an optional OR REPLACE clause. If you specify OR REPLACE, you can recreate an existing PL/SQL unit by changing its declaration or definition without first dropping it. You can then regain object privileges previously granted to the PL/SQL unit. If you redefine a PL/SQL unit, the database will recompile it.

ALTER

The ALTER statement is used to recompile a PL/SQL unit without having to recreate it (changing its declaration or definition). The supported SQL statements include ALTER FUNCTION, ALTER PACKAGE, ALTER PROCEDURE, ALTER TRIGGER, and ALTER TYPE.

You can use the ALTER statement for the following purposes:

• Explicitly recompiles invalid stored units to avoid implicit recompilation at run time and runtime compilation errors and performance overheads.

• Recompile the stored unit with different compilation options.

• Enable or disable triggers.

DROP

The DROP statement deletes an existing PL unit.

SQL statements that you can use include DROP FUNCTION, DROP PACKAGE, DROP PROCEDURE, DROP TRIGGER, the DROP TYPE statement, and DROP TYPE BODY.