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

Beginner

Q:

4-1.

The built-in SQLERRM (b) displays the error text associated with a particular error. If it is used without any arguments, it returns a description of the most recently raised error. If an error code is provided, it returns the description of that error, as illustrated here:

SQL> EXEC DBMS_OUTPUT.PUT_LINE (SQLERRM (-1855))
ORA-01855: AM/A.M. or PM/P.M. required

Q:

4-2.

To solve this problem, you need to:

  • Add an exception section to the end of the block.

  • Include a handler for the NO_DATA_FOUND exception.

  • Call the SQLERRM function to retrieve the error message.

Here is the modified block:

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 || '!');
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;

Q:

4-3.

Each exception has associated with it an error number and an error description or message. An optional attribute is the name of an exception; not all exceptions have a name.

Q:

4-4.

These exceptions are predefined by Oracle in the STANDARD package: VALUE_ERROR, NO_DATA_FOUND, INVALID_NUMBER, TOO_MANY_ROWS.

These identifiers do not name predefined exceptions: DATA_NOT_FOUND (should be NO_DATA_FOUND), DIVIDE_BY_ZERO (should be ZERO_DIVIDE), DUP_KEY_IN_INDEX (should be DUP_VAL_ON_INDEX), CURSOR_OPEN (should be CURSOR_ALREADY_OPEN), and VALUE_TOO_LARGE (should be VALUE_ERROR).

Q:

4-5.

The NO_DATA_FOUND exception is represented by ...

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