O'Reilly logo

Oracle PL/SQL Programming: A Developer's Workbook by Andrew Odewahn, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 4. Exception Handling

Unlike politicians, who can resort to tired phrases like “Mistakes were made,” PL/SQL developers must make sure their programs behave responsibly in the face of the unpredictable. For example, a database server can run out of memory; a user can attempt to insert a duplicate value for a primary key; a SELECT…INTO clause can return too many rows. You can use exception handlers to trap, or handle, these exceptions.

There are two steps to handling an exception:

  1. Define the conditions that represent exceptions; you can supplement the extensive Oracle-supplied set (for example, DUP_VAL_ON_INDEX) by creating exceptions of your own (for example, PROFIT_TOO_LOW), by associating unnamed exceptions with your own names (via the EXCEPTION_INIT pragma), or even by defining your own unnamed exceptions.

  2. Create an exception handling section in your code, where you associate a subset of named exceptions with corresponding blocks of code called handlers.

When an exception occurs (whether it’s generated by the system or you use the RAISE command to create it), program control immediately branches to the handler associated with that exception. If there is no handler for that particular exception (or no exception section at all), you have an unhandled exception, in which case the program terminates immediately and returns the error to the original caller. This chapter tests your ability to define your own named exceptions, create an exception section in your code, and understand how exceptions propagate from one block to the next.

Beginner

4-1.

Which of the following functions can you call to retrieve a text description of the most recent error that has occurred?

  1. SHOWERR

  2. SQLERRM

  3. SQL_ERROR_MESSAGE

4-2.

Change the following block so that it traps a NO_DATA_FOUND exception and displays a description of the current error:

DECLARE
   my_flavor ice_cream.fav_flavor%TYPE;
BEGIN
   SELECT fav_flavor
     INTO my_flavor
     FROM ice_cream
    WHERE name = USER;
   DBMS_OUTPUT.PUT_LINE
      ('I love ' || my_flavor || '!');
END;

4-3.

What are the two attributes of (pieces of information associated with) every exception? What third attribute is optional?

4-4.

Which of the following named exceptions are defined by PL/SQL?

  1. DATA_NOT_FOUND

  2. VALUE_ERROR

  3. NO_DATA_FOUND

  4. DIVIDE_BY_ZERO

  5. INVALID_NUMBER

  6. TOO_MANY_ROWS

  7. DUP_KEY_IN_INDEX

  8. CURSOR_OPEN

  9. VALUE_TOO_LARGE

4-5.

What is the only error that has two different error numbers?

4-6.

Where are the predefined, named exceptions defined?

4-7.

What is the error code and the error message of a user-defined exception?

4-8.

What are the only two positive error numbers used by Oracle?

4-9.

What does it mean for an exception to go “unhandled”?

4-10.

Do you have to supply an exception section in your programs and blocks of code?

4-11.

What special exception handler can you provide that will trap any error that is raised in a block of code?

4-12.

What do you see on your screen (a message delivered via DBMS_OUTPUT, or an unhandled exception, or perhaps nothing at all) when you execute this block?

DECLARE
   my_dream VARCHAR2(5);
BEGIN
   my_dream := 'JUSTICE';
END;

4-13.

What do you see on your screen (a message delivered via DBMS_OUTPUT, or an unhandled exception, or perhaps nothing at all) when you execute this block?

DECLARE
   my_dream VARCHAR2(5);
BEGIN
   BEGIN
      my_dream := 'JUSTICE';
   EXCEPTION
      WHEN VALUE_ERROR
      THEN
         DBMS_OUTPUT.PUT_LINE  ('Inner block');
   END;
   DBMS_OUTPUT.PUT_LINE ('Dream deferred...');
END;

4-14.

What do you see on your screen (a message delivered via DBMS_OUTPUT, or an unhandled exception, or perhaps nothing at all) when you execute this block?

DECLARE
   my_dream VARCHAR2(10) := 'JUSTICE';
BEGIN
   DECLARE
      reality VARCHAR2(3) := 'MILLIONS STARVE';
   BEGIN
      my_dream := 'PEACE';
   EXCEPTION
      WHEN VALUE_ERROR
      THEN
         DBMS_OUTPUT.PUT_LINE  ('Inner block');
   END;
EXCEPTION
   WHEN VALUE_ERROR
      THEN DBMS_OUTPUT.PUT_LINE  ('Outer block');
END;

4-15.

In each of the following examples, there is an error that prevents compilation. What’s wrong with each sample?

  1. EXCEPTION
       WHEN ANY THEN
  2. EXCEPTION
       WHEN VALUE_ERROR AND NO_DATA_FOUND THEN
  3. EXCEPTION
       WHEN OTHERS THEN
          NULL;
       WHEN VALUE_ERROR THEN
          NULL;
  4. BEGIN
       do_stuff;
    WHEN OTHERS
    THEN
       NULL;
    
    END;
  5. BEGIN
       ...
    EXCEPTION
      WHEN -1403 THEN
        NULL;
    END;

4-16.

How can you reraise the current exception from within an exception handler clause? Why would you want to do so?

4-17.

You write the following block of code, declaring your own local exception and handling it as well. Under what circumstances is the exception raised?

FUNCTION big_name (name_in IN VARCHAR2)
   RETURN VARCHAR2
IS
  no_name EXCEPTION;
  name_in_caps VARCHAR2(100);
BEGIN
  name_in_caps := UPPER (name_in);

  RETURN (name_in_caps);
EXCEPTION
  WHEN no_name THEN
    DBMS_OUTPUT.PUT_LINE ('You must supply a name');
END;

4-18.

The following code was written by your coworker Kristopher, who is conveniently on vacation, when you are asked to enhance the code. The users want a specialized message displayed when an invalid value is supplied for the sex parameter:

FUNCTION build_name (name_in IN VARCHAR2, sex_in IN VARCHAR2)
RETURN VARCHAR2 IS
  name_out VARCHAR2(100);
BEGIN
  IF first_char = 'M' THEN
    name_out := 'Mr. ' || name_in;

  ELSIF first_char = 'F' THEN
    name_out := 'Mrs. ' || name_in;
  END IF;
  RETURN (name_out);
END;

Add the code necessary to display the text “A valid sex must be provided” whenever the parameter sex_in is neither “M” nor “F”.

4-19.

Employee SMITH currently earns a salary of $800, and ALLEN earns a salary of $1,600. What will the salaries be for SMITH and ALLEN after the following anonymous block of code executes:

BEGIN
  UPDATE EMP SET sal = sal * 2 where ename = 'SMITH';

  RAISE VALUE_ERROR;

  UPDATE EMP SET sal = sal * 2 where ename = 'ALLEN';
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE ('We had an error');
END;

4-20.

The following anonymous block of code is executed directly from SQL*Plus by the user SCOTT. What will the salaries be for SMITH and ALLEN be after its execution?

BEGIN
  UPDATE EMP SET sal = sal * 2 where ename = 'SMITH';
  RAISE VALUE_ERROR;
  UPDATE EMP SET sal = sal * 2 where ename = 'ALLEN';
END;
/

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required