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

 

 

   
 

PL/SQL Forward Declarations
Oracle Tips by Burleson
 

In the event that you have more than one local declared procedure or function within a block of PL/SQL and the procedures must reference each other, you won’t be able to compile your code without getting an error. Each local procedure must be declared before the other.

To get around this problem, you can use a forward declaration to specify the interface for the procedures. This will allow your PL/SQL block to compile without errors. Listing 4.4 illustrates the use of a forward declaration.

Listing 4.4 Using a forward declaration for a local procedure.

CREATE OR REPLACE
PROCEDURE Calculate_Lot_Size (nWidth   IN     number,
                              nLength  IN     number,
                              nLotSize    OUT number)
 
IS
 
   PROCEDURE Convert_Feet_To_Yards (nFeet  IN     number,
                                    nYards IN OUT number);
 
   PROCEDURE Convert_Yards_To_Feet (nYards IN     number,
                                    nFeet  IN OUT number)
 
   IS
 
     nCheckCalc         number := 0;
     xBAD_CALCULATION   EXCEPTION;
 
   BEGIN
     nFeet := nYards * 3;
 
     Convert_Feet_To_Yards (nFeet  => nFeet,
                            nYards => iCheckCalc);
 
     IF (nCheckCalc != nYards) THEN
        RAISE xBAD_CALCULATION;
     END IF;
   END Convert_Yards_To_Feet;
 
   PROCEDURE Convert_Feet_To_Yards (nFeet  IN     number,
                                    nYards IN OUT number)
 
  IS
 
    nCheckCalc         number := 0;
    xBAD_CALCULATION   EXCEPTION;
 
  BEGIN
    nYards := nFeet/3;
 
    Convert_Yards_To_Feet (nYards => nYards,
                           nFeet  => iCheckCalc);
 
    IF (nCheckCalc != nFeet) THEN
        RAISE xBAD_CALCULATION;
    END IF;
  END Convert_Feet_To_Yards;
BEGIN
  <statements>
END Calculate_Lot_Size;
/

In this example, the first highlighted portion of the code is the forward declaration of the Convert_Feet_To_Yards() procedure, the second highlighted portion of the code is the call to the procedure, and the last highlighted portion of the code is the definition of the procedure’s logic. If the forward declaration of the procedure Convert_Feet_To_Yards() were removed from this example, the code would not compile.

      
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