||Oracle Tips by Burleson
Alteration of Procedures and
To alter the logic or variables of a
procedure or function, use the CREATE OR REPLACE form of the command
in the previous subsection. The only option for the ALTER command
for functions and procedures is COMPILE. This option recompiles the
procedure or function after a modification to their referenced
objects has occurred. The format of this command is available in the
SQL Reference Manual, at the documentation website,
If a function or procedure that has been
invalidated by a change to a table, view, or other referenced
procedure or function is called, it is automatically recompiled.
Whenever possible, explicit recompilation via the ALTER command
should be used. This will pinpoint any problems before the users
find them for you. The script in Source 7.7 will provide a list of
invalid database objects upon execution. Use this script to pinpoint
which packages, procedures, functions, or views need to be
See Code Depot
Copyright © 1996 - 2014 by
Burleson. All rights reserved.
Oracle® is the registered trademark
of Oracle Corporation.