Chapter Eighteen Exception Handling

1. Chapter EighteenException Handling Objective: Define exceptions List types of exception handlers Trap errors Exception messages & customization Blocks & exception propagation Chapter 18: Exception Handling

2. Introduction DECLARE PE_ratio NUMBER(3,1); BEGIN SELECT Price/earning INTO PE_ratio FROM Stock WHERE Symbol = ‘BBY’; INSERT INTO table1(Symbol, ratio) VALUES (‘BBY’, PE_ratio); COMMIT; EXCEPTION WHEN ZERO_DIVIDE THEN INSERT INTO table1(Symbol, ratio) VALUES (‘BBY’, NULL); COMMIT; WHEN OTHERS THEN ROLLBACK; END; Chapter 18: Exception Handling

3. Adapting an Exception Handling Strategy • How to log an error? • How to report an error to users? • When to log an error? • When to report an error to users? • Who should receive the error log & how to make correction? • How to handle the transaction after an error occurred? • Where should I declare my error handling: • For each block • Only for top level Chapter 18: Exception Handling

4. Definition Of Exception Handling: • “Methods of a program that react and deal with runtime errors”. • ORA-1000 Unique constraint violated • ORA-06502 PL/SQL: numeric or value error • ORA-01403 No data is retrieved from SELECT statement (NO_DATA_FOUND) Chapter 18: Exception Handling

5. Exception Handling • Facts: • Exception is an identifier in PL/SQL • Exception mechanism of PL/SQL is the same as ADA • It is similar to JAVA: but unlike JAVA, exception is not an object • Exceptions are designed for run-time error handling -not compile time. • Unlike C, Oracle system has error handling methods Chapter 18: Exception Handling

6. Exception Handling How do we handle exceptions: • Trap the exception • Propagate it to the calling environment Chapter 18: Exception Handling

7. Handling Exceptions Trap the Exception DECLARE ….. BEGIN …. EXCEPTION ….. END Propagate the Exception DECLARE ….. BEGIN …. EXCEPTION … END Exception propagates to calling environment Chapter 18: Exception Handling

8. Exception Handling Trap the exception: DECLARE …. BEGIN …. <Exception is raised> EXCEPTION …. <Exception is trapped> END If exception is not handled successfully, block terminates with failure and the exception propagates to the calling block Chapter 18: Exception Handling

9. Exception Types • Predefined Oracle Server • Non-predefined Oracle Server • User-defined Chapter 18: Exception Handling

10. Exception Types: • Predefined Oracle Server errors: • No declaration is needed • Oracle Server raises them implicitly ORA-01403 NO_DATA_FOUND ORA-01422 TOO_MANY_ROWS ORA-01476 ZERO_DIVIDE ORA-06500 STORAGE_ERROR ORA-06530 ACCESS_INTO_NULL ORA-06592 CASE_NOT_FOUND ORA-00001 DUP_VAL_ON_INDEX Chapter 18: Exception Handling

11. Exception Types: • Non_Predefined Oracle Server errors • Other Standard Oracle Server errors • Declared within the declarative section • Oracle Server raises them implicitly Chapter 18: Exception Handling

12. Exception Types: • User_defined • User determines an abnormal condition • Declared in declaration section • It is raised explicitly DECLARE e_TooManyNumbers EXCEPTION; Chapter 18: Exception Handling

13. Raising Exceptions: • When an error occurs, an exception is raised • User_defined exceptions are raised explicitly via RAISE command • Other exceptions are raised by EXCEPTION_INIT pragma Chapter 18: Exception Handling

14. Raising USER_DEFINED Exceptions • Example DECLARE e_TooManyNumbers EXCEPTION; V_NoStudent NUMBER(4); V_MaxStudent NUMBER(4); BEGIN SELECT Current_Students, Max_Students INTO V_NoStudent, V_MaxStudent FROM classes WHERE C_Num=641 AND Dept=‘COSC’; IF V_NoStudent > V_MaxStudent THEN RAISE e_TooManyNumbers; END IF; END; Chapter 18: Exception Handling

15. Ways an Exception may be Raised • Oracle raises an exception when it detects an error • User may raise an exception with RAISE • User may raise an exception with RAISE_APPLICATION_ERROR built in procedure Chapter 18: Exception Handling

16. Type of RAISE Statement • RAISE exception_name; • RAISE Package_name.exception; • RAISE; Chapter 18: Exception Handling

17. Example DECLARE invalid_id EXCEPTION; id_value VARCHAR2; BEGIN id_value := id_for(‘Mike’); IF id_value BETWEEN 1111 AND 2222 THEN …. ELSE RAISE invalid_id; END IF; END; • RAISE ZERO_DIVIDE; -- Predefined Oracle exception Chapter 18: Exception Handling

18. Re-raise Exceptions BEGIN BEGIN IF X=-10 THEN RAISE X_IS_LOW; END IF; EXCEPTION WHEN X_IS_LOW THEN DBMS_OUTPUT.PUT_LINE (X || ’is low’); RAISE; END … EXCEPTION WHEN X_IS_LOW THEN --Handle Error Here END; Chapter 18: Exception Handling

19. Example • RAISE; • Use this form if you want to re-raise the same exception from within an exception handler EXCEPTION WHEN My_error THEN DBMS_OUTPUT.PUT_LINE(‘Error description’); --pass on the exception to the enclosing block RAISE; END; Chapter 18: Exception Handling

20. RAISE_APPLICATION_ERROR(Customize The Error Messages) Syntax RAISE_APPLICATION_ERROR(err_No, Message) [, TRUE | FALSE]; • It lets you issue a non standard user defined error message from stored subprograms • Called only from an executing stored subprogram ….. BEGIN SELECT count(*) INTO x FROM USERTABLE; IF x<100 THEN RAISE_APPLICATION_ERROR(-20202, ‘Expect at least 100 rows’); ELSE …. END; Chapter 18: Exception Handling

21. Example BEGIN DELETE FROM dept WHERE deptNo = V_deptNo; IF SQL%NOTFOUND THEN -- in execution part RAISE_APPLICATION_ERROR(-20002, ‘Error in deleting depart no.’); END IF; …… Chapter 18: Exception Handling

22. Raising Predefined Exceptions • Example BEGIN INSERT INTO Students (id, name) VALUES (111, ‘Mark’); INSERT INTO Students (id, name) VALUES (111, ‘Mary’); DUP_VAL_ON_INDEX Chapter 18: Exception Handling

23. 1-Trapping Exceptions Syntax EXCEPTION WHEN exception1 THEN statement1; statement2; … [WHEN exception2 THEN statement1; statement2; …] [WHEN OTHERS THEN statement1; statement2; ….] Chapter 18: Exception Handling

24. Trapping Exceptions Guidelines • EXCEPTION Keyword starts exception handling section • Several exception handlers are allowed • Only one handler is processed before leaving the block • WHEN OTHERS is the Last clause: • Exceptions can not appear in assignment statement or SQL statement Chapter 18: Exception Handling

25. Several Places can Raise the Same Exception: DECLARE e_TooManyNumbers EXCEPTION; V_NoStudent NUMBER(4); V_MaxStudent NUMBER(4); BEGIN SELECT Current_Students, Max_Students INTO V_NoStudent, V_MaxStudent FROM classes WHERE C_Num=641 AND Dept=‘COSC’; IF V_Student > V_MaxStudent THEN RAISE e_TooManyNumbers; END IF; Continued Chapter 18: Exception Handling

26. Example EXCEPTION WHEN e_TooManyNumbers THEN INSERT INTO log_file (info) VALUES (‘COSC 641 has:‘ || V_NoStudent || ‘Max No is:’ || V_MaxStudent); END; Chapter 18: Exception Handling

27. Two or More Exceptions Executing the Same Sequence of Statements IF … RAISE a; IF … RAISE b; … EXCEPTION WHEN a or b or c THEN … Chapter 18: Exception Handling

28. Example EXCEPTION WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN INSERT INTO log_file (info) VALUES (‘ ‘); WHEN OTHERS THEN INSERT INTO log_file (info) VALUES (‘ ‘); END; Chapter 18: Exception Handling

29. 2 - Trapping Non_Predefined Oracle Server Errors • Declare the exception first • Code the PRAGMA EXCEPTION_INIT • Handle the raised exception Chapter 18: Exception Handling

30. Non-Predefined Error Syntax exceptionName EXCEPTION; … PRAGMA EXCEPTION_INIT(exceptionName, error_No); Chapter 18: Exception Handling

31. Example DECLARE e_Faculty_Remaining EXCEPTION; PRAGMA EXCEPTION_INIT (e_Faculty_Remaining, -6501); V_deptNo dept.deptNo%TYPE := &p_deptno; BEGIN DELETE FROM dept WHERE deptNo = V_deptNo; COMMIT; EXCEPTION WHEN e_faculty_Remaining THEN DBMS_OUTPUT.PUT_LINE(‘can not remove dept, there are faculty in the dept’); END; Chapter 18: Exception Handling

32. 3 - Trapping User_defined Exceptions • Declare the exception • Explicitly raise the exception by using RAISE statement • Handle the raised exception Chapter 18: Exception Handling

33. User defined Error Syntax exceptionName EXCEPTION: … RAISE exceptionName; Chapter 18: Exception Handling

34. Example DECLARE e_Invalid_product EXCEPTION; BEGIN UPDATE Product SET desc= ‘&Product_desc’ WHERE ProductId = &Product_No; IF SQL%NOTFOUND THEN RAISE e_Invalid_Product; END IF; COMMIT; EXCEPTION WHEN e_Invalid_Product THEN DBMS_OUTPUT.PUT_LINE(‘Invalid Product Numbers’); END; Chapter 18: Exception Handling

35. Calling Environments Chapter 18: Exception Handling

36. Example of Propagating Exceptions: DECLARE a EXCEPTION; b EXCEPTION; PARAGMA EXCEPTION_INIT(b, -2292); BEGIN FOR counter IN fac_cursor LOOP BEGIN SELECT … UPDATE … IF SQL%NOTFOUND THEN RAISE a; END IF; EXCEPTION WHEN b THEN …. WHEN a THEN …. END; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN …. WHEN TOO_MANY_ROWS THEN …. END; Chapter 18: Exception Handling

37. Exception Propagation: BEGIN BEGIN IF NUM=-1 THEN RAISE A; ELSIF NUM=1 THEN RAISE B; ELSE RAISE C; EXCEPTION WHEN A THEN … END; … EXCEPTION WHEN B THEN … END; Chapter 18: Exception Handling

38. Declarations Exception: DECLARE Max CONSTANT NUMBER(2):=999;--exception BEGIN NULL; EXCEPTION WHEN OTHERS THEN … END; / Chapter 18: Exception Handling

39. Handlers Exception in Exception Clause: . . . . EXCEPTION WHEN INVALID_NUMBER THEN INSERT INTO … --RAISE DUP_VAL_ON_INDEX WHEN DUP_VAL_ON_INDEX THEN …. END; Chapter 18: Exception Handling

40. PRACTICE: • Write a procedure to insert name and salary of faculty members whose salary is plus or minus $x (x is passed as a parameter) of the salary, into faculty_stat table with the following exceptions: • If there is no faculty salary in that range, write a message into logfile table ‘No faculty in $x range’ • If there is more that one faculty, write the sum of faculty members in that range. • Any other exception should go to the logfile with the appropriate message. Chapter 18: Exception Handling

41. Functions for Trapping Exceptions SQLCODE Returns the numeric value for the last error raised SQLERRM Returns the message associated with the error number Chapter 18: Exception Handling

42. Example DECLARE a NUMBER; b VARCHAR2(256); BEGIN ….. EXCEPTION .…. WHEN OTHERS THEN ROLLBACK; a:= SQLCODE; b:= SQLERRM; INSERT INTO errors VALUES (a, b); END; Chapter 18: Exception Handling

43. Example CREATE OR REPLACE PACKAGE dynamicSQL Is invalid_Table_Name EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_Table_Name, -903); invalid_Col_Name EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_Col_Name, -904); . . . END dynamicSQL; To Trap: WHEN dynamicSQL.invalid_Col_Name THEN …… Chapter 18: Exception Handling

44. Example CREATE OR REPLACE PACKAGE ErrorNo IS Error_One CONSTANT NUMBER:=-20050; Exc_One EXCEPTION; PRAGMA EXCEPTION_INIT(Exc_One, Error_One); Error_Two CONSTANT NUMBER:=-20051; Exc_Two EXCEPTION; PRAGMA EXCEPTION_INIT(Exc_Two, Error_Two); END ErrorNo; PROCEDURE … is …. BEGIN IF … THEN RAISE_APPLICATION_ERROR(ErrorNo.Error_One, ‘Description of Error’); END IF; Chapter 18: Exception Handling

45. Calling a Procedure or Block to Check for Errors • SQL>SET SERVEROUTPUT ON SIZE 1000000 FORMAT TRUNCATED • SQL>EXEC deposit(1111, 250, ‘check’ ); Chapter 18: Exception Handling

46. Finding the Location of Errors BEGIN SELECT ….. SELECT….. SELECT…. EXCEPTION …….. END; Chapter 18: Exception Handling

47. Method 1: Finding the location of Errors BEGIN SELECT ….. v_counter:=1; SELECT….. v_counter:=2; SELECT…. v_counter:=3; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO errors VALUES (‘Error in statement ‘ || v_counter); END; Chapter 18: Exception Handling

48. Method 2: Put Each Exception in a Block BEGIN SELECT ….. EXCEPTION… END; BEGIN SELECT….. EXCEPTION END;…… Chapter 18: Exception Handling

49. CALL STACK V_callstack VARCHAR2(2000); BEGIN V_callstack := DBMS_UTILITY.FORMAT_CALL_STACK; Chapter 18: Exception Handling

50. Capture Rows that Cause Errors: • Create a table called EXCEPTIONS in your schema • Script is called ‘utlexcpt.sql’ and is in /rdbms/admin directory • Exceptions table contains four columns: Row_ID, Owner, Table_Name, Constraint. Chapter 18: Exception Handling