Previous Table of Contents Next


PL/SQL allows variables to be declared dynamically, depending on the type of a referenced column or variable. Consider this code:

vFirstName     STUDENTS.first_name%TYPE;
Students_rec   Students_cur%ROWTYPE;

The vFirstName variable will have the same datatype and size as the column first_name in the STUDENTS table. If the length of the first_name column is increased, the length of vFirstName will also be increased. The use of %TYPE allows you to develop a variable that is dependent on the structure of your data; if the structure of the data changes, your variable will change as well.

Using %TYPE references to variables is an excellent idea for making maintenance of your code simpler; however, this is not a complete solution. Imagine that you have a variable referencing a column of type integer ; if this changes to type number or one of the other subtypes of type number, %TYPE has done its job.

Now imagine that your variable is a %TYPE reference to a number column, perhaps a unique sequence number for records. If a business rule changes, you may suddenly find yourself with a sequence that includes characters and a block of code that calls a numeric function such as min() or max(). In this instance, your code will have to change.

Admittedly, this is a fairly radical change in the data model that your code is based upon, but it has been known to occur. Using %TYPE is a good idea (fields changing in size is quite common, especially during system development), but don’t expect %TYPE to solve all your future maintenance woes.

%ROWTYPE is very similar to %TYPE, except the it creates a variable of record type. %ROWTYPE is typically used with cursors, but can duplicate the structure of a table or record as well.

Exceptions And Exception Handling

PL/SQL was originally based on the Ada programming language (once widely used in software written for the Department of Defense). One of the primary reasons that Ada was chosen as the mother language for PL/SQL is the concept of exceptions. Figure 2.10 illustrates how exceptions are raised and handled.

Figure 2.10  How PL/SQL handles exceptions.

When a PL/SQL block encounters an error condition, an exception is raised. Each PL/SQL block can have an exception handler prior to the END statement. In this section of the block, the developer specifies what actions should be taken for specific exceptions. If no exception handler is declared in a block, the exception is raised to the calling PL/SQL block.

Table 2.4 lists the standard exceptions that you will encounter most frequently.

Table 2.4 The five most commonly encountered exceptions.

Exception Situation
NO_DATA_FOUND You executed a query for which no rows were found.
TOO_MANY_ROWS You executed a query for which more than one row was found. Your query was only structured to receive a single row in the result set.
DUP_VAL_ON_INDEX You attempted to insert a row into a table, but the row violates the table’s primary key or unique index.
VALUE_ERROR You assigned a value to a variable that is too short to hold the value. This occurs most commonly with variables of type varchar2 or char, but this can also happen to variables of other types.
INVALID_NUMBER You referenced a value containing a character in an expression that attempted to convert the value to a number, either explicitly or implicitly.

If no exception is raised from a PL/SQL block (or stored PL/SQL object), the calling object assumes that the block completed successfully. Unlike a function written in C, there are no status values that are found in the database environment to indicate if a procedure or function call encountered an error.


Most of the code that you write will be executable code, with one notable exception. A PRAGMA is not executable code; instead, a PRAGMA is an instruction to the PL/SQL compiler. PL/SQL provides two distinct uses of the PRAGMA statement: exception_init and restrict_references.

The exception_init PRAGMA instructs Oracle to assign a name to a standard Oracle error message that does not have an associated named exception. In this example, the ORA-00942 error is renamed to a user-defined exception xTABLEDOESNOTEXIST:

   xTableDoesNotExist    EXCEPTION;
   PRAGMA exception_init (xTableDoesNotExist -942);

The RESTRICT_REFERENCES PRAGMA instructs Oracle about the purity level of a packaged PL/SQL function. In this example, the Conversions package instructs the database that the function Feet_To_Meters does not alter any database or package states:

PACKAGE Conversions


   FUNCTION Feet_To_Meters (nFeet IN     number) RETURN number;
END Conversions;

Once the function has been declared in this fashion, it can be execute inside a DML statement without receiving an error message, as shown in this example:

SELECT Feet_To_Meters (3.45)

There are four purity levels that can be asserted for packaged functions:

  WNDS—The function does not write to any tables.
  RNDS—The function does not read from any tables.
  WNPS—The function does not modify any variables inside the package.
  RNPS—The function does not read any variables inside the package.

User-Defined Exceptions

PL/SQL allows developers to define their own exceptions. This is a useful method of returning a specific status to calling procedures and functions. Giving user-defined exceptions meaningful names helps make code easier to understand. User-defined exceptions must be explicitly raised through the use of the RAISE statement. Listing 2.16 illustrates how user-defined exceptions are declared and raised.

Listing 2.16 A user-defined exception.

   xStudentNotEligibleForAid    EXCEPTION;

IF <condition> THEN
   RAISE xStudentNotEligibleForAid;

An exception raised using the RAISE statement is processed just like any exception raised by Oracle.

Previous Table of Contents Next