Week 2

Day 11

More Programming Techniques with PL/SQL

Today's lesson is the final discussion of SQL and PL/SQL before you venture into the use of Developer/2000 and Power Objects in building applications. The material in this lesson falls into three categories:

• How to handle errors in a PL/SQL subprogram. You'll learn how to define an exception handler for Oracle errors and user-defined exceptions. Also, you'll see some examples of pre-defined exceptions.
• Declaring and using cursors in a PL/SQL subprogram to retrieve multiple rows from the database.
• Using PL/SQL to construct a database trigger. You'll learn how to use a trigger to validate data, enforce security, or record changes made to a table.

Handling Errors in PL/SQL

The Oracle Error Messages and Codes manual lists all error codes and messages, not including operating-specific errors. At some point, your application will probably encounter some of these errors. In PL/SQL, Oracle errors are referred to as exceptions. Some of the exceptions have pre-defined names that can be referenced in PL/SQL subprograms. In addition to these pre-defined Oracle exceptions, you can define application-specific exceptions in a PL/SQL subprogram.

NEW TERM
An exception is a pre-defined or user-defined application error that is raised auto-matically by the Oracle RDBMS or raised intentionally in a PL/SQL subprogram.

One method for handling errors in a PL/SQL subprogram is to check for any Oracle error code after each SQL statement. The problem with that approach is that the resulting subprogram can be difficult to follow. As an alternative, PL/SQL enables you to specify what processing should take place for a particular exception. This section of the PL/SQL subprogram is called the exception section of a PL/SQL subprogram. A pre-defined exception is said to be "raised" when an Oracle error occurs during the execution of a PL/SQL subprogram. You raise a user-defined exception by invoking the RAISE statement at an appropriate location in the PL/SQL code.

For example, suppose you have written a PL/SQL subprogram that loads information from a flat file into a table in the database. If the flat file contains an invalid number—for example, 3.1A instead of 3.14—a pre-defined exception will be raised when the PL/SQL subprogram tries to insert a row with the invalid number.

The Exception Section

The exception section is an optional section of a PL/SQL subprogram that tells PL/SQL how to handle particular exceptions.

The syntax for the exception section is as follows:

EXCEPTION
  WHEN exception-name1 THEN
    PL/SQL-statements;
  ...
  WHEN exception-nameN THEN
    PL/SQL-statements;
  ...
  [WHEN OTHERS THEN
    PL/SQL-statements;]
END;

The variables are defined as follows:

exception-name1 through exception-nameN are the names of pre-defined and user-defined exceptions.

PL/SQL-statements is one or more PL/SQL statements that are executed when the exception is raised.

To illustrate, Listing 11.1 contains a PL/SQL block with an exception section. Note that the exception section contains two exception handlers: one for a pre-defined exception—the TOO_MANY_ROWS exception (in line 11)—and one for all other exceptions—signified by the word OTHERS (in line 13).

Listing 11.1. Handling the TOO_MANY_ROWS exception.

SQL> declare
  2
  2  Course_Rec   Course%ROWTYPE;
  3
  3  begin
  4
  4  dbms_output.enable;
  5
  5  select *
  6  into Course_Rec
  7  from Course
  8  where
  9  Department_ID = `BIO';
 10
 10  exception
 11    when TOO_MANY_ROWS then
 12      dbms_output.put_line(`TOO_MANY_ROWS raised - use a cursor');
 13    when OTHERS then
 14      NULL;
 15  end;
 16  /
TOO_MANY_ROWS raised - use a cursor

If you remove the exception handler for OTHERS and cause an exception to be raised that doesn't have an exception handler, PL/SQL returns an error message. Listing 11.2 shows an example—a string of 18 characters is assigned to a variable that can store up to five characters, resulting in an Oracle error.

Listing 11.2. Exception section doesn't handle OTHERS exceptions.

SQL> declare
  2
  2  xyz   varchar2(5);
  3
  3  begin
  4
  4  dbms_output.enable;
  5
  5  xyz := `This will not fit!';
  6
  6  exception
  7
  7    when TOO_MANY_ROWS then
  8      dbms_output.put_line(`TOO_MANY_ROWS Exception Raised');
  9      dbms_output.put_line(`Occurred in anonymous block');
 10
 10  end;
 11  /
declare
 *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5

Pre-Defined Exceptions

All exceptions can be categorized as either pre-defined or user defined. Pre-defined exceptions are automatically raised; for example, an SQL statement that references a table that doesn't exist results in an Oracle error. As an example, your PL/SQL subprogram may contain a SELECT statement that, in some circumstances, returns no rows; this will cause the NO_DATA_FOUND exception to be raised. Pre-defined exceptions have meaningful names. Here are some pre-defined exceptions that you might encounter when developing an Oracle application:

• DUP_VAL_ON_INDEX
• INVALID_NUMBER
• NO_DATA_FOUND
• TOO_MANY_ROWS
• VALUE_ERROR

The following sections look at which condition can trigger each of these pre-defined exceptions.

The DUP_VAL_ON_INDEX Exception

The DUP_VAL_ON_INDEX is raised when an SQL statement attempts to create a duplicate value in a column on which a unique index exists. To illustrate, Listing 11.3 contains an anonymous PL/SQL block that tries to update the Course table so that all rows have the same value for Course_ID, thereby raising the DUP_VAL_ON_INDEX exception.

Listing 11.3. Handling the DUP_VAL_ON_INDEX exception.

SQL> declare
  2
  2  begin
  3
  3  dbms_output.enable;
  4
  4  update Course
  5  set
  6  Department_ID = `BIO',
  7  Course_ID = `101';
  8
  8  exception
  9    when DUP_VAL_ON_INDEX then
 10      dbms_output.put_line(`DUP_VAL_ON_INDEX exception raised');
 11  end;
 12  /
DUP_VAL_ON_INDEX exception raised

PL/SQL procedure successfully completed.

The INVALID_NUMBER Exception

The INVALID_NUMBER exception is raised when an SQL statement specifies an invalid number. For instance, Listing 11.4 provides an example of an anonymous PL/SQL block that attempts to update the Additional Fees column in the Course table. The exception is raised because the To_Number function attempts to convert a string variable, Bogus_Value, to a number.

Listing 11.4. Handling the INVALID_NUMBER exception.

SQL> declare
  2
  2  Bogus_Value   varchar2(30) := `NOT A NUMBER';
  3
  3  begin
  4
  4  dbms_output.enable;
  5
  5  update Course
  6  set

                                                  continues