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




Oracle PL/SQL Pseudocode
Oracle Tips by Burleson

It’s often useful to write pseudocode when designing a new procedure. Pseudocode should outline the logical steps of a procedure. Listing 4.26 contains pseudocode for the Calculate_GPA() procedure.

Listing 4.26 Pseudocode for the Calculate_GPA() procedure.

for each class taken by the student loop
  if the class is not being audited and earns credit then
     get the credit points earned for the class;
     get the total hours for the class;
     determine the number of credit points earned for the class;
     add hours for the class to credited hours taken total;
     add credit points to a running total;
end loop;
update the STUDENTS table, setting the overall_gpa column
  to the value of the total credit points earned divided by
  the total number of credited hours taken;

Creating this type of logical map for a procedure shortens the time required to write the procedure and also allows non-developers (perhaps those who are more familiar with the business rules but are not developers) to look at the logic of the procedure and spot errors.

Pseudocode for procedures often serves as a map for commenting the procedure, as well.


A good comment in source code can describe the functionality of code as effectively as pages and pages of written documentation outside the code. Good comments describe why the code does what it does, often explaining how the code enforces business rules. Consider the following two sample comments:

Comment A:

-- If the account balance isn't at least $100, we can't upgrade the
-- account to Gold level.
IF (nBalance < 100) THEN
  Raise_Application_Error (-20343, 'The balance is too low.');

Comment B:

-- If nBalance < 100, raise an error.
IF (nBalance < 100) THEN
  Raise_Application_Error (-20343, 'The balance is too low.');

Comment A explains why the code is written this way. Comment B paraphrases the code but doesn’t explain the business rules behind the code. There is very little in Comment B that will help you understand the purpose of the code.

TIP:  Single-Line versus Multi-Line Comments

Although PL/SQL supports both single-line and multi-line comments, it’s a good idea to only use single-line comments in your code, because, at some point, you might need to comment out a large block of code. PL/SQL doesn’t support nested C-style comments.

The exception to this rule is when you’re working in a 3GL language using one of Oracle’s precompilers. In this situation, you should use the commenting style that is specified by your coding standards for the 3GL, because the Oracle precompilers often don’t recognize the single-line style of commenting.


Take a look at this block of code and see if you can recognize the purpose of the variables:

IF (x < 100) THEN
  Raise_Application_Error (-20343, 'The balance is too low.');;

You might recognize this code as the same code described just a few lines ago, sans comments and with different variable names. Keeping track of x in three lines of code is easy, but keeping track of x in a 200-line procedure is another story entirely.

Using meaningful identifier names is the best way to document code, as well as one of the easiest. Six months down the road, you might have to debug your 200-line procedure that uses x, y, and z for variable names.

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