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:
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.
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.
Rewrite the following PL/SQL block so that it allows each of the following SQL DML statements to execute, even if any of the others fail:
BEGIN UPDATE emp SET empno = 100 WHERE empno > 5000; DELETE FROM dept WHERE deptno = 10; DELETE FROM emp WHERE deptno = 10; END;
Write a PL/SQL block that handles by name the following Oracle error:
ORA-01014: ORACLE shutdown in progress.
The exception handler should display an appropriate message and then reraise the exception.
Which of the following uses of the EXCEPTION_INIT pragma are valid, which are invalid, and why?
What shows up on your screen when you run the following block of code in your execution environment as a top-level PL/SQL block? Explain the behavior.
DECLARE d VARCHAR2(1); no_data_found EXCEPTION; BEGIN SELECT dummy INTO d FROM dual WHERE 1=2; IF d IS NULL THEN RAISE no_data_found; END IF; EXCEPTION WHEN no_data_found THEN DBMS_OUTPUT.PUT_LINE ('Trapped the error!?'); END;
First, compile the valerr package as shown here (can you imagine a more poorly named function?):
/* Filename on web page: valerr.pkg */ CREATE OR REPLACE PACKAGE valerr IS FUNCTION get RETURN VARCHAR2; END valerr; / CREATE OR REPLACE PACKAGE BODY valerr IS v VARCHAR2(1) := 'ABC'; FUNCTION get RETURN VARCHAR2 IS BEGIN RETURN v; END; BEGIN DBMS_OUTPUT.PUT_LINE ('Before I show you v...'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Trapped the error!'); END valerr; /
Then call DBMS_OUTPUT.PUT_LINE to display the value returned by the valerr.get function as follows:
SQL> EXEC DBMS_OUTPUT.PUT_LINE ('Value of v is ' || valerr.get);
What information is displayed on the screen? What is displayed if you execute the same line of code a second time?
You are working on a program that requires exclusive access to data in the EMP table. You attempt to lock the rows using a SELECT FOR UPDATE. If you cannot immediately acquire the lock, you should simply print a message to try again later. If you do acquire the lock, print the name of each employee using a loop.
While helping a friend time his new program, you decide to calculate the elapsed time from executing the code as follows (note that you are better off using DBMS_UTILITY.GET_TIME instead of SYSDATE):
DECLARE start_time DATE; end_time DATE; BEGIN start_time := SYSDATE; DBMS_OUTPUT.PUT_LINE ('Filler code...'); end_time := SYSDATE; DBMS_OUTPUT.PUT_LINE (end_time - start_time); END;
When you execute this block, you receive the following output, but you can successfully execute the new procedure on its own:
* ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "SYS.STANDARD", line 1027
What could be the problem?
Just about the most important information you can obtain about an error is the line number on which the error is raised. Which of the following statements accurately describes ways to obtain this information?
You have been asked to record all errors that occur in your application so they can be examined later. Identify all the different forms your error log can take inside a PL/SQL environment.
You have decided to write your error information to a database table. You create a procedure to write to the log as follows:
/* Filename on web page: log.sql */ CREATE OR REPLACE PACKAGE logpkg IS PROCEDURE putline (code_in IN INTEGER, text_in IN VARCHAR2); END; / CREATE OR REPLACE PACKAGE BODY logpkg IS CURSOR sess IS SELECT MACHINE, PROGRAM FROM V$SESSION WHERE AUDSID = USERENV('SESSIONID'); rec sess%ROWTYPE; PROCEDURE putline ( code_in IN INTEGER, text_in IN VARCHAR2) IS BEGIN INSERT INTO logtab VALUES (code_in, text_in, SYSDATE, USER, SYSDATE, USER, rec.machine, rec.program); END; BEGIN OPEN sess; FETCH sess INTO rec; CLOSE sess; END; /
You then test the log mechanism as follows:
SQL> DECLARE 2 myval NUMBER; 3 BEGIN 4 myval := 'abc'; 5 EXCEPTION 6 WHEN OTHERS 7 THEN 8 logpkg.putline (SQLCODE, SQLERRM); 9 RAISE; 10 END; 11 / DECLARE * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 9
But there is nothing in your log!
SQL> SELECT count(*) FROM logtab; COUNT(*) --------- 0
Why didn’t the log package insert a row into the log table?
Associate the type of error log in the first list with the characteristics in the second list:
Type of error log:
Write a procedure that raises any error number passed to it, whether it be an Oracle error number (like ORA-01855), an application-specific error (in the –20,XXX range), or your own positive error numbers. (Perhaps you did not want to be constrained by the 1,000 error numbers Oracle offers. You noticed, instead, that the only positive numbers used for error handling are 1 and 100, leaving an awful lot for you.)