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

Intermediate

4-21.

When an exception is raised in a block of code, a number of actions occur. Organize the following actions in the correct order, and identify those that do not occur:

Control is passed to the exception section, if it exists.
Oracle goes to the package STANDARD to determine how to handle the error.
The executable section terminates immediately.
If there is no match and no WHEN OTHERS clause, propagate the exception out to the enclosing block.
If after executing handler code, an exception is raised (or reraised), propagate the exception out to the enclosing block.
If there is no match for the error, check for a WHEN OTHERS section and execute that handler code.
If after executing handler code, an exception is raised (or reraised), and there is no enclosing block, propagate the exception out to the calling environment.
Oracle logs the exception to the alert log for the database.
If there is an exception section, find a match for the error and execute that handler code.

4-22.

The built-in RAISE_APPLICATION_ERROR accepts three parameters: the error number to be used, the text of the error message, and whether or not to clear the error stack. List all the restrictions on each of these parameters.

4-23.

The built-in RAISE_APPLICATION_ERROR can programmatically signal an error condition. Identify which of the following calls to RAISE_APPLICATION_ERROR are valid, and which are not. For those that are not valid, explain the problem:

  1. RAISE_APPLICATION_ERROR (20001, ‘Invalid employee number!’);

  2. RAISE_APPLICATION_ERROR (‘Invalid Social Security Number.’);

  3. RAISE_APPLICATION_ERROR (NO_DATA_FOUND);

  4. RAISE_APPLICATION_ERROR (-20023, ‘Date must not be in the future’);

  5. RAISE_APPLICATION_ERROR (-21001, ‘Invalid date.’);

  6. RAISE_APPLICATION_ERROR (-20002, ‘Invalid product code.’, ‘TRUE’);

  7. RAISE_APPLICATION_ERROR (-20002, ‘Invalid product code.’, FALSE);

4-24.

True or false? After you call RAISE_APPLICATION_ERROR to raise an application-specific error, you can call the APPLICATION_ERROR_CODE and APPLICATION_ERROR_MESSAGE functions to retrieve the error number and string passed to RAISE_APPLICATION_ERROR.

4-25.

While talking with your boss, you mention casually how you think you could improve upon the current calc_commission function that was written by your predecessor. “If I were to write it,” you say, “I would make it more flexible and more bulletproof.” “What a great idea,” says your boss, “by the end of today, trap the condition when an employee has no sales (or negative sales), and display a custom message.”

FUNCTION calc_commission (sales_in IN NUMBER)
   RETURN NUMBER
IS
   commission NUMBER(5,2) := 0;
BEGIN
   RETURN (sales_in * 0.1);
END;

4-26.

The following function accepts a first and last name and returns a formatted version of the name:

FUNCTION format_name (
   surname_in IN VARCHAR2,
   given_name_in IN VARCHAR2)
RETURN VARCHAR2 IS
  no_surname EXCEPTION;
  no_given_name EXCEPTION;
  formatted_name VARCHAR2(100);
BEGIN
  IF surname_in IS NULL THEN raise no_surname;

  ELSIF given_name_in IS NULL THEN raise no_given_name;

  ELSE
    formatted_name :=
       surname_in || ', ' ||
       SUBSTR (given_name_in, 1, 1);
  END IF;

  RETURN (formatted_name);
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR
       (-20001, 'Invalid name supplied.');
END;

How does the output differ when the surname is not supplied, as opposed to when the given name is not supplied?

4-27.

How would you change the program in 4-26 to take advantage of the locally defined exceptions?

4-28.

Which of the following code segments trap the exception raised and provide the most information about the error that occurred? What is the difference between the two code blocks, and which provides greater control?

DECLARE
  bad_data EXCEPTION;
BEGIN
  RAISE bad_data;
EXCEPTION
  WHEN bad_data THEN
    DBMS_OUTPUT.PUT_LINE ('data was bad: ' || SQLCODE);
END;

DECLARE
  bad_data EXCEPTION;
BEGIN
  RAISE bad_data;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE ('data was bad: ' || SQLCODE);
END;

4-29.

Your manager has been so impressed with the previous improvements you made to the calc_commission function that he is just brimming with new ideas to improve it. In fact, he has already promised these improvements to his boss, and she expects them for the release going out tomorrow morning. One new requirement is that the function should optionally accept a second parameter, representing the amount of commission to be applied. Another is that the commission should be specified as a percentage (0 to 100), and that the condition of negative or zero sales should be handled as a custom exception. If the rate is not supplied, assume 10% commission. The same message should be displayed in the sales error condition.

Given the current version of the following calc_commission function, make the appropriate changes to satisfy the new requirements:

FUNCTION calc_commission (sales_in IN NUMBER)
  RETURN NUMBER
IS
  commission NUMBER(5,2) := 0;
BEGIN
  IF sales_in <= 0 THEN
    RAISE_APPLICATION_ERROR (-20001, 'Pull your own weight');
  ELSE
    RETURN (sales_in * 0.1);
  END IF;
END;

4-30.

Laxmi of Technical Support at your company calls; a user has reported that the following program is failing. It “just suddenly stopped working,” and it works for some clients but not others:

FUNCTION is_overdue (due_date IN DATE, paid_date IN DATE)
   RETURN BOOLEAN
IS
   days_between NUMBER(2) := due_date - paid_date;
BEGIN
   RETURN days_between > 30;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE
      ('Error in is_overdue; Check input data.');
END;

You are sure this module is being executed, but the error message is never displayed. Instead, when an error occurs, it goes unhandled, and the user sees this incomprehensible “dump”:

SQL> DECLARE
  2     gotAproblem BOOLEAN;
  3  BEGIN
  4     gotAproblem := is_overdue (SYSDATE+400, SYSDATE);
  5  END;
  6  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error:
   number precision too large

Why isn’t the exception section trapping the error? Why is the error intermittent? How can you fix this program?

4-31.

Your friend Jennifer comes to you at 4:45 on Friday (as usual) and asks you to look at the following code:

BEGIN

  BEGIN
    ...
    RAISE NO_DATA_FOUND;
    ...
  EXCEPTION
    WHEN VALUE_ERROR THEN
      DBMS_OUTPUT.PUT_LINE ('Value Error Alert!');
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE ('Other Alert!');
  END;

  IF SQLCODE != 0 THEN
    DBMS_OUTPUT.PUT_LINE ('We ran into some problems');
  END IF;

END;

Jennifer can’t figure out why her second debug statement (“We ran into some problems”) is never being displayed. No matter what exception is raised, all she sees is:

Other Alert!

Why is she not seeing her second debug statement?

4-32.

Joe arrives on your doorstep with a puzzled look. “I keep getting this ORA-12154 error and have no idea what it is and what causes it.” What is the easiest way of obtaining a description of the Oracle error message?

4-33.

Under what circumstances might a NO_DATA_FOUND exception be raised?

4-34.

Consider the following program:


/* Filename on web page: whodidthat.sql */
CREATE OR REPLACE PROCEDURE who_did_that (
   emp_in IN emp.empno%TYPE)
IS
   v_ename emp.ename%TYPE;

   line VARCHAR2(1023);
   fid UTL_FILE.FILE_TYPE;

   list_of_numbers PLVtab.number_table;
BEGIN
   SELECT ename INTO v_ename
     FROM emp
    WHERE empno = emp_in;

   DBMS_OUTPUT.PUT_LINE (v_ename);
   fid := UTL_FILE.FOPEN ('c:\temp', 'notme.sql', 'R');
   UTL_FILE.GET_LINE (fid, line);
   UTL_FILE.GET_LINE (fid, line);

   IF list_of_numbers (100) > 0
   THEN
      DBMS_OUTPUT.PUT_LINE ('Positive value at row 100');
   END IF;

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.PUT_LINE ('Who did that?');
END who_did_that;
/

This program could raise the NO_DATA_FOUND exception in one of three ways, yet the exception section can’t tell us “who did that”—which part of the code caused the problem. Rewrite this procedure so that the exception section contains three different handlers for each potential cause of this error.

4-35.

What do you see on your screen (a message delivered via DBMS_OUTPUT, or an unhandled exception, or no error at all) when you execute the following code segments?

DECLARE
   string_of_5_chars VARCHAR2(5);
BEGIN
   BEGIN
      string_of_5_chars := 'Daniel';
   EXCEPTION
      WHEN VALUE_ERROR
      THEN
         RAISE NO_DATA_FOUND;

      WHEN NO_DATA_FOUND
      THEN
         DBMS_OUTPUT.PUT_LINE  ('Inner block');
   END;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.PUT_LINE  ('Outer block');
END;

4-36.

Suppose that you execute the following code segments:

DECLARE
  counter INTEGER;
BEGIN
  DBMS_OUTPUT.PUT('Starting test, ');

  BEGIN
    SELECT * INTO counter
    FROM dual
    WHERE 1 = 2;

    DBMS_OUTPUT.PUT('found data, ');
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT('No data in inner block, ');
      RAISE;
  END;

  DBMS_OUTPUT.PUT_LINE ('Finished test.');
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('No data in outer block');

  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE ('Caught OTHER exception');
END;

What do you see on your screen?

  1. Starting test, found data, Finished test

  2. Starting test, No data in inner block, Finished test

  3. Starting test, No data in inner block, No data in outer block

  4. Starting test, No data in inner block, Caught OTHER exception

  5. Starting test, No data in inner block, No data in outer block, Finished test

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