Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Creating and Dropping Procedures
Oracle Tips by Burleson
 

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
PROCEDURE Annual_Review
 
IS
  <declarations>
 
BEGIN
  <statements>;
END Annual_Review;
/

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 will occur.

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 Chapter 8.

Recompiling Procedures

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 chapter.


Automatically 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 developers.


      
This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

††
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter