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



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.
-- Date            Author        Reason for Change
-- ----------------------------------------------------------------
-- 02/28/1997      J. Schmoe     Procedure created.
-- ****************************************************************;
   CURSOR StudentClasses_cur
   SELECT SC.course_number, C.course_hours
          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
      iCreditsForClass integer := 0;
     SELECT decode (course_grade, 'A', 4,
                                  'B', 3,
                                  'C', 2,
                                  'D', 1, 0)
     INTO   iCreditsForClass
     WHERE  course_number = iCourseID
     AND    ssn           = iStudentSSN;
     RETURN iCreditsForClass;
   END Get_Course_Credits;
  FOR StudentClasses_rec IN StudentClasses_cur LOOP
    iCourse     := StudentClasses_rec.course_number;
    iTotalHours := StudentClasses_rec.course_hours;
    iTotalCredits :=   iTotalCredits
                  + Get_Course_Credits (iCourse);
  SET    overall_gpa = (iTotalCredits / iTotalHours)
  WHERE  ssn = iStudentSSN;
      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