Creating and Dropping
Oracle Tips by
Procedures are created in SQL*Plus using a command like the one
shown in Listing 4.2.
Listing 4.2 Creating a stored procedure.
CREATE OR REPLACE
Using the OR REPLACE clause indicates that Oracle should
replace an existing procedure of the same name if it exists. If the
object exists and the OR REPLACE clause isnít used, an error
Procedures can be dropped using the DROP command inside
SQL*Plus, as follows:
DROP PROCEDURE Annual_Review;
Procedures can also be created and dropped using Oracleís Procedure
Builder or one of several available third-party editors. These
products have become quite sophisticated.
Itís very rare to compile a procedure (or other stored object)
successfully on the first time through; very often you will receive
compilation errors. Resolving compilation errors is discussed in
While procedures (and other objects) can be recompiled by simply
reissuing the CREATE OR REPLACE command for the object, thereís
another method of recompiling stored objects without having a copy of
the source code in a standalone file.
A stored PL/SQL object can be recompiled by using an ALTER
command in SQL*Plus, like the commands shown here:
ALTER PROCEDURE Annual_Review COMPILE;
ALTER FUNCTION Raise_Salary COMPILE;
ALTER PACKAGE End_Of_Year COMPILE;
ALTER PACKAGE BODY End_Of_Year COMPILE;
In order to recompile objects using this method, you must be
running under an account with the ALTER ANY OBJECT privilege.
This method of recompiling stored PL/SQL objects is useful when
objects become marked as invalid due to changes to their dependent
objects. Dependencies are discussed in detail a bit later in this
Recompiling Invalid Stored Objects
When an object that your procedure references is altered in some
manner (a column in a table is modified, a new column is added, or a
stored PL/SQL object called by your procedure is recompiled), your
procedure will be marked as invalid because Oracle is uncertain of its
status. The next time your procedure is called, Oracle will generate
an error message and remove the p-code for the invalid procedure. When
your procedure is called again, Oracle will automatically attempt to
recompile the p-code.
A package spec isnít marked as
invalid when a package body is altered. However, altering the package
spec does invalidate the package body.
There isnít any way that Iíve found
to force Oracle to automatically recompile the p-code for an invalid
stored procedure or function without first generating an error. This
minor annoyance is a relatively common complaint among PL/SQL
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor.