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




A PL/SQL Wish List
Oracle Tips by Burleson

PL/SQL is not a perfect programming language (someone from Oracle told me in 1992 that it wasn’t really a programming language at all). If you have any experience with other programming languages, you’ll find that some really useful things are missing from PL/SQL. While these aren’t serious deficiencies, their incorporation would make PL/SQL a bit less tedious to use at times.

The continue Statement

The continue statement in C is a marvelous tool, allowing you to immediately skip to the next iteration of a loop without executing any steps that occur inside the loop after the continue statement. Unfortunately, PL/SQL has only a GOTO statement to allow you to accomplish this simple task. The GOTO statement is illustrated by this example:

  ...PL/SQL statements..

You can also mirror this functionality by putting the contents of your loop inside a stored procedure and using the RETURN statement to exit out of the stored procedure if you determine that your data can’t or shouldn’t be processed any further. This example calls a procedure that holds the internal logic of the loop.

   MyLoopContents (parameter1 => value);

Listing 2.21 illustrates a loop that simulates the functionality of a continue statement by using a stored procedure.

Listing 2.21 Using a stored procedure to simulate a C continue statement.

PROCEDURE MyLoopContents (parameter1    <datatype>)
   <variable declarations>
    ...PL/SQL statements...
   IF some_condition THEN
   END IF;

The case/switch Statement

While not an essential part of any language, the case statement is more elegant than:

IF (x < 100) THEN
ELSIF (x < 300) THEN

Unfortunately, PL/SQL allows only the clumsy IF-THEN-ELSE method of testing multiple conditions.

Incrementing Variables

The ability to increment or decrement a variable in C using the ++, --, +=, and -= operators is something that I’ve often wished for while coding a procedure or function. Instead, you’ll have to settle for the less elegant method shown here:

nSum := nSum + 1;

While functionally equivalent, the previous example simply seems less graceful than a similar C statement:


You can create a stored function that accepts an integer value and returns the value incremented by one, although it hardly seems worth the effort.


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