CHAPTER 5 EXCEPTION HANDLING

1. CHAPTER 5EXCEPTION HANDLING

2. PL/SQL BLOCK STRUCTURE PL/SQL block has the following structure: DECLARE Declaration statements BEGIN Executable statements EXCEPTION Exception-handling statements END ;

3. EXCEPTION-HANDLING SECTION The exception-handling section is the last section of the PL/SQL block. This section contains statements that are executed when a runtime error occurs within a block. Runtime errors occur while the program is running and cannot be detected by the PL/SQL compiler. EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE (‘ There is no student with student id 123 ’); END;

4. System exceptions Named Unnamed User-defined exceptions Types of exceptions

5. Oracle can handle: CURSOR_ALREADY_OPENED (sqlcode = -6511) DUP_VAL_ON_INDEX (sqlcode = -0001) INVALID_CURSOR (sqlcode = -1001) INVALID_NUMBER (sqlcode = -1722) LOGIN_DENIED (sqlcode = -1017) NO_DATA_FOUND (sqlcode = -1403) TOO_MANY_ROWS (sqlcode = -1422) …etc… These are named in the ‘standard’ package in pl/sql. Named system exceptions

6. These exception names do not need to be declared. To handle them explicitly, put a clause in the exception section: EXCEPTION When DUP_VAL_ON_INDEX Then dbms_output.put_line(‘record already there’); END; To handle these exceptions explicitly:

7. Example DECLARE v_num1 integer := &sv_num1; v_num2 integer := &sv_num2; v_result number; BEGIN v_result := v_num1 / v_num2; DBMS_OUTPUT.PUT_LINE (‘v_result: ’|| v_result); EXCEPTION WHEN ZERO_DIVIDETHEN DBMS_OUTPUT.PUT_LINE (‘A number cannot be divided by zero.’); END;

8. EXAMPLE DECLARE v_student_id char(5) := &sv_student_id; v_first_name VARCHAR2(35); v_last_name VARCHAR2(35); BEGIN SELECT first, last INTO v_first_name, v_last_name FROM student WHERE studentid = v_student_id; DBMS_OUTPUT.PUT_LINE ('Student name: '||v_first_name||' '||v_last_name); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('There is no such student'); END;

9. So far, you have seen examples of the programs able to handle a single exception only. • For example, a PL/SQL contains an exception-handler with a single exception ZERO_DIVIDE. • However, many times in the PL/SQL block you need to handle different exceptions. • Moreover, often you need to specify different actions that must be taken when a particular exception is raised. HANDLING DIFFERENT EXCEPTIONS

10. DECLARE v_student_id NUMBER := &sv_student_id; v_enrolled VARCHAR2(3) := 'NO'; BEGIN DBMS_OUTPUT.PUT_LINE (‘Check if the student is enrolled’); SELECT ‘YES’ INTO v_enrolled FROM enrollment WHERE student_id = v_student_id; DBMS_OUTPUT.PUT_LINE (‘The student is enrolled into one course’); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(‘The student is not enrolled’); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE (‘The student is enrolled into many courses’); END; • This example contains two exceptions in the single exception handling section. • The first exception, NO_DATA_FOUND, will be raised if there are no records in the ENROLLMENT table for a particular student. • The second exception, TOO_MANY_ROWS, will be raised if a particular student is enrolled into more than one course.

11. You have seen examples of exception-handling sections that have particular exceptions, such as NO_DATA_FOUND or ZERO_DIVIDE. • However, you cannot always predict beforehand what exception might be raised by your PL/SQL block. • In cases like this, there is a special exception handler called OTHERS. • All predefined Oracle errors (exceptions) can be handled with the help of the OTHERS handler. OTHERS Handler

12. When run, this example produces the following output: • Enter value for sv_instructor_id: 100 • old 2: v_instructor_id NUMBER := &sv_instructor_id; • new 2: v_instructor_id NUMBER := 100; • An error has occurred • PL/SQL procedure successfully completed. • This demonstrates not only the use of the OTHERS exception handler, but also a bad programming practice. • The exception OTHERS has been raised because there is no record in the INSTRUCTOR table for instructor ID 100. Example DECLARE v_instructor_id NUMBER := &sv_instructor_id; v_instructor_name VARCHAR2(50); BEGIN SELECT first_name||' '||last_name INTO v_instructor_name FROM instructor WHERE instructor_id = v_instructor_id; DBMS_OUTPUT.PUT_LINE (‘Instructor name is’ ||v_instructor_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘An error has occurred’); END;

13. These errors are not pre-named, but have a number. They will be raised automatically by the RDBMS. The EXCEPTION section handles them in the WHEN OTHERS clause. To name an unnamed error in your application: Give the error a name using a PRAGMA, or compiler directive, called EXCEPTION_INIT. PL/SQL or RDBMS raise the error automatically. Handle the error in a specially written WHEN clause in the exception section. Unnamed system exceptions

14. We’ve all seen errors that Oracle throws at us: ERROR: ORA=12170: TNS: Connect timeout occurred TNS Listener does not currently know of service requested in connect descriptor Note: All of these errors have an error number: e.g. ORA = 12170 means that the connection timeout occurred. These errors are RAISED automatically by the system, because they are system errors. Unnamed system exceptions

15. DECLARE deadlock_detectedEXCEPTION; PRAGMA EXCEPTION_INIT(deadlock_detected, -60); BEGIN ... -- Some operation that causes an ORA-00060 -- error (see locking) EXCEPTION WHEN deadlock_detected THEN -- handle the error END; Example

16. User Defined Exceptions This type of exception is called a user-defined exception because it is defined by the programmer. Before the exception can be used, it must be declared. A user-defined exception is declared in the declaration part of a PL/SQL block as shown below: DECLARE exception_name EXCEPTION; Once an exception has been declared, the executable statements associated with this exception are specified in the exception-handling section of the block. The format of the exception-handling section is the same as for named exceptions.

17. Example DECLARE e_invalid_id EXCEPTION; BEGIN … EXCEPTION WHEN e_invalid_id THEN DBMS_OUTPUT.PUT_LINE ('An id cannot be negative'); END;

18. DECLARE exception_name EXCEPTION; BEGIN … IF CONDITION THEN RAISE exception_name; ELSE … END IF; EXCEPTION WHEN exception_name THEN ERROR-PROCESSING STATEMENTS; END; Raising Exception A user-defined exception must be raised explicitly. In other words, you need to specify in your program under which circumstances an exception must be raised as shown :