Handling Exceptions

1. Handling Exceptions

2. Objectives • After completing this lesson, you should be able to do the following: • Define PL/SQL exceptions • Recognize unhandled exceptions • List and use different types of PL/SQL exception handlers • Trap unanticipated errors • Describe the effect of exception propagation in nested blocks • Customize PL/SQL exception messages

3. Example of an Exception SET SERVEROUTPUT ON DECLARE lname VARCHAR2(15); BEGIN SELECT last_name INTO lname FROM employees WHERE first_name='John'; DBMS_OUTPUT.PUT_LINE ('John''s last name is : ' ||lname); END; /

4. Example of an Exception SET SERVEROUTPUT ON DECLARE lname VARCHAR2(15); BEGIN SELECT last_name INTO lname FROM employees WHERE first_name='John'; DBMS_OUTPUT.PUT_LINE ('John''s last name is : ' ||lname); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE (' Your select statement retrieved multiple rows. Consider using a cursor.'); END; /

5. Handling Exceptions with PL/SQL • An exception is a PL/SQL error that is raised during program execution (at run-time) • An exception can be raised: • Implicitly by the Oracle server • Explicitly by the program • An exception can be handled: • By trapping it with a handler • By propagating it to the calling environment

6. Handling Exceptions Is theexception trapped? Terminate abruptly. No Yes Exception is raised. Execute statementsin the EXCEPTIONsection. Propagate the exception. Terminate gracefully.

7. Exception Types } • Predefined Oracle server • Non-predefined Oracle server • User-defined Implicitly raised Explicitly raised

8. Trapping Exceptions • Syntax: EXCEPTION WHEN exception1 [OR exception2 . . .] THEN statement1; statement2; . . . [WHEN exception3 [OR exception4 . . .] THEN statement1; statement2; . . .] [WHEN OTHERS THEN statement1; statement2; . . .]

9. Guidelines for Trapping Exceptions • The EXCEPTION keyword starts the exception handling section. • Several exception handlers are allowed. • Only one handler is processed before leaving the block. • WHENOTHERS is the last clause.

10. Trapping Predefined Oracle Server Errors • Reference the predefined name in the exception-handling routine. • Sample predefined exceptions: • NO_DATA_FOUND • TOO_MANY_ROWS • INVALID_CURSOR • ZERO_DIVIDE • CURSOR_ALREADY_OPEN http://www.techonthenet.com/oracle/errors/index.php

11. An Example • DECLARE • stock_price NUMBER := 9.73; • net_earnings NUMBER := 0; • pe_ratio NUMBER; • BEGIN • pe_ratio := stock_price / net_earnings; • DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio); • EXCEPTION • WHEN ZERO_DIVIDE THEN • DBMS_OUTPUT.PUT_LINE('Company must have had zero earnings.'); • pe_ratio := NULL; • WHEN OTHERS THEN • DBMS_OUTPUT.PUT_LINE('Some other kind of error occurred.'); • pe_ratio := NULL; • END; • /

12. Declare Trapping Non-Predefined Oracle Server Errors Reference Associate Declarative section EXCEPTION section Name theexception. Use PRAGMA EXCEPTION_INIT. Handle the raised exception.

13. Non-Predefined Error • To trap Oracle server error number –01400(“cannot insert NULL”): SET SERVEROUTPUT ON DECLARE insert_excep EXCEPTION; PRAGMA EXCEPTION_INIT (insert_excep, -01400); BEGIN INSERT INTO departments (department_id, department_name) VALUES (280, NULL); EXCEPTION WHEN insert_excep THEN DBMS_OUTPUT.PUT_LINE('INSERT OPERATION FAILED'); DBMS_OUTPUT.PUT_LINE(SQLERRM); END;/ 1 2 3

14. Functions for Trapping Exceptions • SQLCODE: Returns the numeric value for the error code • SQLERRM: Returns the message associated with the error number

15. Functions for Trapping Exceptions • Example DECLARE error_code NUMBER; error_message VARCHAR2(255); BEGIN ... EXCEPTION ... WHEN OTHERS THEN ROLLBACK; error_code := SQLCODE ; error_message := SQLERRM ; INSERT INTO errors (e_user, e_date, error_code, error_message) VALUES(USER,SYSDATE,error_code, error_message); END; /

16. Example Declare s_child_exists EXCEPTION; PRAGMA EXCEPTION_INIT(s_child_exists,-2292) Begin delete from s where sno=‘S1’; Exception WHEN s_child_exists THEN dbms_output.put_line(‘Delete the sp_test record first ‘); End;

17. Trapping User-Defined Exceptions Declare Raise Reference Declarative section Executable section Exception-handling section Name theexception. Explicitly raise the exception by using the RAISE statement. Handle the raised exception.

18. Trapping User-Defined Exceptions ... ACCEPT deptno PROMPT 'Please enter the department number:' ACCEPT name PROMPT 'Please enter the department name:'DECLARE invalid_department EXCEPTION; name VARCHAR2(20):='&name'; deptno NUMBER :=&deptno; BEGIN UPDATE departments SET department_name = name WHERE department_id = deptno; IF SQL%NOTFOUND THEN RAISE invalid_department; END IF; COMMIT; EXCEPTION WHEN invalid_department THEN DBMS_OUTPUT.PUT_LINE('No such department id.'); END; / 1 2 3

19. Calling Environments

20. Propagating Exceptions in a Subblock DECLARE . . . no_rows exception; integrity exception; PRAGMA EXCEPTION_INIT (integrity, -2292); BEGIN FOR c_record IN emp_cursor LOOP BEGIN SELECT ... UPDATE ... IF SQL%NOTFOUND THEN RAISE no_rows; END IF; END; Subblocks can handle an exception or pass the exception to the enclosing block. END LOOP; EXCEPTION WHEN integrity THEN ... WHEN no_rows THEN ... END; /

21. Propagating Exceptions in a Subblock- An Example • DECLARE • past_due EXCEPTION; • acct_num NUMBER; • BEGIN • DECLARE ---------- sub-block begins • past_due EXCEPTION; -- this declaration prevails • acct_num NUMBER; • due_date DATE := SYSDATE - 1; • todays_date DATE := SYSDATE; • BEGIN • IF due_date < todays_date THEN • RAISE past_due; -- this is not handled • END IF; • END; ------------- sub-block ends • EXCEPTION • -- Does not handle raised exception • WHEN past_due THEN • DBMS_OUTPUT.PUT_LINE • ('Handling PAST_DUE exception.'); • WHEN OTHERS THEN • DBMS_OUTPUT.PUT_LINE • ('Could not recognize PAST_DUE_EXCEPTION in this scope.'); • END; • /

22. Scope of an exception : • DECLARE • v_student_id NUMBER := &sv_student_id; • v_name VARCHAR2(30); • BEGIN • SELECT RTRIM(first_name)||' '||RTRIM(last_name) • INTO v_name • FROM student • WHERE student_id = v_student_id; • DBMS_OUTPUT.PUT_LINE ('Student name is '||v_name); • EXCEPTION • WHEN NO_DATA_FOUND THEN • DBMS_OUTPUT.PUT_LINE ('There is no such student'); • END; • => • The scope of an exception is the portion of the block that is covered by this exception.

23. Nested Blocks • DECLARE • v_student_id NUMBER := &sv_student_id; • v_name VARCHAR2(30); • v_total NUMBER(1); • -- outer block • BEGIN • SELECT RTRIM(first_name)||' '||RTRIM(last_name) • INTO v_name • FROM student • WHERE student_id = v_student_id; • DBMS_OUTPUT.PUT_LINE ('Student name is '||v_name); • -- inner block • BEGIN • SELECT COUNT(*) • INTO v_total • FROM enrollment • WHERE student_id = v_student_id; • DBMS_OUTPUT.PUT_LINE ('Student is registered for '|| v_total||' course(s)'); • EXCEPTION • WHEN VALUE_ERROR OR INVALID_NUMBER THEN • DBMS_OUTPUT.PUT_LINE ('An error has occurred'); • END; • EXCEPTION • WHEN NO_DATA_FOUND THEN • DBMS_OUTPUT.PUT_LINE ('There is no such student'); • END; • The above example will never raise the exception NO_DATA_FOUND in the inner block. Why

24. DECLARE • v_student_id NUMBER := &sv_student_id; • v_name VARCHAR2(30); • v_registered CHAR; • -- outer block • BEGIN • SELECT RTRIM(first_name)||' '||RTRIM(last_name) • INTO v_name • FROM student • WHERE student_id = v_student_id; • DBMS_OUTPUT.PUT_LINE ('Student name is '||v_name); • -- inner block • BEGIN • SELECT 'Y' INTO v_registered • FROM enrollment • WHERE student_id = v_student_id; • DBMS_OUTPUT.PUT_LINE ('Student is registered'); • EXCEPTION • WHEN VALUE_ERROR OR INVALID_NUMBER THEN • DBMS_OUTPUT.PUT_LINE ('An error has occurred'); • END; • EXCEPTION • WHEN NO_DATA_FOUND THEN • DBMS_OUTPUT.PUT_LINE ('There is no such student'); • END; • Result : • Enter value for sv_student_id: 284 • old 2: v_student_id NUMBER := &sv_student_id; • new 2: v_student_id NUMBER := 284; • Student name is Salewa Lindeman • There is no such student • PL/SQL procedure successfully completed.

25. Exception Propagation : • DECLARE • v_test_var CHAR(3):= 'ABCDE'; • BEGIN • DBMS_OUTPUT.PUT_LINE ('This is a test'); • EXCEPTION • WHEN INVALID_NUMBER OR VALUE_ERROR THEN • DBMS_OUTPUT.PUT_LINE ('An error has occurred'); • END; • When executed, this example produces the output shown: • DECLARE • * • ERROR at line 1: • ORA-06502: PL/SQL: numeric or value error: character string buffer too small • ORA-06512: at line 2 • => when a runtime error occurs in the declaration section of the PL/SQL block, • the exception-handling section of this block is not able to catch the error.

26. --outer block • BEGIN • -- inner block • DECLARE • v_test_var CHAR(3):= 'ABCDE'; • BEGIN • DBMS_OUTPUT.PUT_LINE ('This is a test'); • EXCEPTION • WHEN INVALID_NUMBER OR VALUE_ERROR THEN • DBMS_OUTPUT.PUT_LINE ('An error has occurred in '|| • 'the inner block'); • END; • EXCEPTION • WHEN INVALID_NUMBER OR VALUE_ERROR THEN • DBMS_OUTPUT.PUT_LINE ('An error has occurred in the program'); • END; • When executed, this example produces the output shown: • An error has occurred in the program • PL/SQL procedure successfully completed. • => when a runtime error occurs in the declaration section of the inner block, • the exception immediately propagates to the enclosing (outer) block.

27. DECLARE • v_test_var CHAR(3) := 'ABC'; • BEGIN • v_test_var := '1234'; • DBMS_OUTPUT.PUT_LINE ('v_test_var: '||v_test_var); • EXCEPTION • WHEN INVALID_NUMBER OR VALUE_ERROR THEN • v_test_var := 'ABCD'; • DBMS_OUTPUT.PUT_LINE ('An error has occurred'); • END; • When executed, this example produces the output shown: • DECLARE • * • ERROR at line 1: • ORA-06502: PL/SQL: numeric or value error: character string buffer too small • ORA-06512: at line 8 • ORA-06502: PL/SQL: numeric or value error: character string buffer too small • => when a runtime error occurs in the exception-handling section • of the PL/SQL block, the exception-handling section of this block is not able to prevent the error.

28. Propagating Exceptions in a Subblock

29. Propagating Exceptions in a Subblock

30. RAISE_APPLICATION_ERROR Procedure • Syntax: • You can use this procedure to issue user-defined error messages from stored subprograms. • You can report errors to your application and avoid returning unhandled exceptions. raise_application_error (error_number, message[, {TRUE | FALSE}]);

31. RAISE_APPLICATION_ERROR Procedure • Used in two different places: • Executable section • Exception section • Returns error conditions to the user in a manner consistent with other Oracle server errors

32. RAISE_APPLICATION_ERROR Procedure Executable section: • Exception section: BEGIN ... DELETE FROM employees WHERE manager_id = v_mgr; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20202, 'This is not a valid manager'); END IF; ... ... EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20201, 'Manager is not a valid employee.'); END;/

33. Summary • In this lesson, you should have learned how to: • Define PL/SQL exceptions • Add an EXCEPTION section to the PL/SQL block to deal with exceptions at run time • Handle different types of exceptions: • Predefined exceptions • Non-predefined exceptions • User-defined exceptions • Propagate exceptions in nested blocks and call applications