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

 

 

   
 

Documenting Procedures
Oracle Tips by Burleson
 

The essential elements of documentation are the same for both procedures and functions. Your documentation must cover the following three basic aspects of the procedure:

  • Purpose — What business rules does the procedure enforce? Are there any special situations that the procedure has to handle?
  • Parameters — What are the parameters, and how are they used? Are any of the parameters restricted with regard to size or values?
  • Error conditions — What exceptions can the procedure propagate to the calling module?

The most common and useful place to document a procedure is within the procedure’s source code. This is accomplished through the use of header text, meaningfully named identifiers, and thorough commenting. It’s also useful to write pseudocode for the procedure before the code is written. This pseudocode can often be turned into a template for the comments that need to be placed in the code.

The Header

The header (or prologue) is intended to handle documentation within the procedure itself. While creating and maintaining a header requires some extra work, the trade-off for this work is easier maintenance in the future. Listing 4.25 contains a sample header for the Calculate_GPA() procedure.

Listing 4.25 The Calculate_GPA() procedure with a header.

PROCEDURE Calculate_GPA (iStudentSSN IN    number)
 
-- ****************************************************************
-- Description: The procedure Calculate_GPA accepts a student's
-- social security number as a parameter, loops through all the
-- classes for the students that are not being audited and are
-- taken for credit, and sums the credit points earned and hours
-- for the class.
--
-- The procedure then updates the overall_gpa column in the
-- STUDENTS table to the value of the total credit points earned
-- divided by the total hours for all classes taken.
--
-- REVISION HISTORY
-- Date            Author        Reason for Change
-- ----------------------------------------------------------------
-- 02/28/1997      J. Schmoe     Procedure created.
-- ****************************************************************;
 
IS
 
   CURSOR StudentClasses_cur
   IS
   SELECT SC.course_number, C.course_hours
   FROM   SCHEDULED_CLASSES SC,
          CLASSES           C
   WHERE  SC.ssn         = iStudentSSN
   AND    C.credit_flag  = 'Y'
   AND    C.audit_flag   = 'N';
 
   iTotalCredits    integer := 0;
   iTotalHours      integer := 0;
 
   FUNCTION Get_Course_Credits (iCourseID IN     integer)
   RETURN integer
 
   IS
 
      iCreditsForClass integer := 0;
 
   BEGIN
     SELECT decode (course_grade, 'A', 4,
                                  'B', 3,
                                  'C', 2,
                                  'D', 1, 0)
     INTO   iCreditsForClass
     FROM   SCHEDULED_CLASSES
     WHERE  course_number = iCourseID
     AND    ssn           = iStudentSSN;
 
     RETURN iCreditsForClass;
   END Get_Course_Credits;
 
BEGIN
  FOR StudentClasses_rec IN StudentClasses_cur LOOP
    iCourse     := StudentClasses_rec.course_number;
    iTotalHours := StudentClasses_rec.course_hours;
 
    iTotalCredits :=   iTotalCredits
                  + Get_Course_Credits (iCourse);
  END LOOP;
 
  UPDATE STUDENTS
  SET    overall_gpa = (iTotalCredits / iTotalHours)
  WHERE  ssn = iStudentSSN;
 
EXCEPTION
  WHEN ZERO_DIVIDE THEN
      System.Log_Error (vObjectName =>'Calculate_GPA',
                        vErrorText  => SQLERRM,
                        vParameters => to_char (iCourseID) ||
                                       '^' ||
                                       to_char (iStudentSSN));
END Calculate_GPA; 

         
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