14 SQL Statements for Stored PL/SQL Units

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

For instructions for reading the syntax diagrams in this chapter, see Oracle Database SQL Language Reference.

CREATE [ OR REPLACE ] Statements

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.

ALTER Statements

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.

  For information about compilation parameters, see "PL/SQL Units and 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.

  For information about editioned and noneditioned objects, see Oracle Database Development Guide.

The ALTER TYPE statement has additional uses. For details, see "ALTER TYPE Statement".

DROP Statements

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