Skip Headers
Oracle® Database PL/SQL Language Reference
12c Release 1 (12.1)

E17622-21
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

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:

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.

Caution:

A CREATE OR REPLACE statement does not issue a warning before replacing the existing PL/SQL unit.

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:

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 these SQL statements: