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.


Each of the following SQL statements creates a PL/SQL unit and stores it in the database:

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

If you want to recompile an existing PL/SQL unit without re-creating it (without changing its declaration or definition), use one of the following SQL statements:

Two reasons to use an ALTER statement are:

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 the following SQL statements: