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. Handling Exceptions with PL/SQL • What is an exception?Identifier in PL/SQL that is raised during execution • How is it raised? • An Oracle error occurs. • You raise it explicitly. • How do you handle it? • Trap it with a handler. • Propagate it to the calling environment.

4. DECLARE DECLARE BEGIN BEGIN EXCEPTION EXCEPTION END; END; Handling Exceptions Propagate the exception • Trap the exception Exception is raised Exception is raised Exception is trapped Exception is not trapped Exception propagates to calling environment

5. } Implicitly raised Exception Types • Predefined Oracle Server • Non-predefined Oracle Server • User-defined Explicitly raised

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

7. Trapping Exceptions Guidelines • WHEN OTHERS is the last clause. • EXCEPTION keyword starts exception-handling section. • Several exception handlers are allowed. • Only one handler is processed before leaving the block.

8. Trapping Predefined Oracle Server Errors • Reference the standard name in the exception-handling routine. • Sample predefined exceptions: • NO_DATA_FOUND • TOO_MANY_ROWS • INVALID_CURSOR • ZERO_DIVIDE • DUP_VAL_ON_INDEX

9. When there is no data returned by row •  declare price item.actualprice%type; begin Select actual price into price from item where qty=888; when no-data-found then dbms_output.put_line('item missing'); end;

10. Predefined Exception • Syntax BEGIN EXCEPTION WHEN NO_DATA_FOUND THEN statement1; statement2; WHEN TOO_MANY_ROWS THEN statement1; WHEN OTHERS THEN statement1; statement2; statement3; END;

11. Declare Associate Reference Trapping Non-Predefined Oracle Server Errors Declarative section Exception-handling section • Name the exception • Code the PRAGMA EXCEPTION_INIT • Handle the raised exception

12. e_emps_remaining EXCEPTION; PRAGMA EXCEPTION_INIT ( e_emps_remaining, -2292); e_emps_remaining Non-Predefined Error • Trap for Oracle Server error number –2292, an integrity constraint violation. DECLARE e_emps_remaining EXCEPTION; PRAGMA EXCEPTION_INIT ( e_emps_remaining, -2292); v_deptno dept.deptno%TYPE := &p_deptno; BEGIN DELETE FROM dept WHERE deptno = v_deptno; COMMIT; EXCEPTION WHEN e_emps_remaining THEN DBMS_OUTPUT.PUT_LINE ('Cannot remove dept ' || TO_CHAR(v_deptno) || '. Employees exist. '); END; 1 2 3

13. Functions for Trapping Exceptions • SQLCODEReturns the numeric value for the error code • SQLERRMReturns the message associated with the error number

14. SQLCODE SQLERRM Functions for Trapping Exceptions DECLARE v_error_code NUMBER; v_error_message VARCHAR2(255); BEGIN ... EXCEPTION ... WHEN OTHERS THEN ROLLBACK; v_error_code := SQLCODE ; v_error_message := SQLERRM ; INSERT INTO errors VALUES(v_error_code, v_error_message); END; • Example

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

16. e_invalid_product EXCEPTION; RAISE e_invalid_product; e_invalid_product User-Defined Exception Example DECLARE e_invalid_product EXCEPTION; BEGIN UPDATE product SET descrip = '&product_description' WHERE prodid = &product_number; IF SQL%NOTFOUND THEN RAISE e_invalid_product; END IF; COMMIT; EXCEPTION WHEN e_invalid_product THEN DBMS_OUTPUT.PUT_LINE('Invalid product number.'); END; 1 2 3

17. EXAMPLE OF USER DEFINED EXCEPTION •  DECLARE  e_rec emp%ROWTYPE;  e1 EXCEPTION;  sal1 emp.sal%TYPE; BEGIN  SELECT sal INTO sal1 FROM emp WHERE deptno = 30 AND ename = 'John';  IF sal1 < 5000 THEN   RAISE e1;   sal1 := 8500;   UPDATE emp SET sal = sal1 WHERE deptno = 30 AND ename = 'John';  END IF; • EXCEPTION   WHEN no_data_found THEN    RAISE_APPLICATION_ERROR (-20001, 'John is not there.');   WHEN e1 THEN    RAISE_APPLICATION_ERROR (-20002, 'Less Salary.'); END;

18. INTERESTING EG OF USER DEFINED EXCEPTIONS Declare zero-price exception; price number(8); begin select actualprice into price from item where ordid =400; if price=0 or price is null then raise zero-price; end if; exception when zero-price then dbms_output.put_line('raised xero-price exception'); end;

19. Calling Environments SQL*Plus Procedure Builder Oracle Developer Forms Precompiler application An enclosing PL/SQL block Displays error number and message to screen Displays error number and message to screen Accesses error number and message in a trigger by means of the ERROR_CODE and ERROR_TEXT packaged functions Accesses exception number throughthe SQLCA data structure Traps exception in exception-handling routine of enclosing block

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

21. RAISE_APPLICATION_ERRORProcedure • Syntax • A procedure that lets you issue user-defined error messages from stored subprograms • Called only from an executing stored subprogram raise_application_error (error_number, message[, {TRUE | FALSE}]);

22. RAISE_APPLICATION_ERRORProcedure • Used in two different places: • Executable section • Exception section • Returns error conditions to the user in a manner consistent with other Oracle Server errors

23. EXAMPLE OF RAISE-APPLICATION-ERROR... THIS IS YOUR OWN ERROR STATEMENT...U RAISE --YOUR OWN ERROR Declare s1 emp.sal %type; begin select sal into s1 from emp where ename='SOMDUTT'; if(no-data-found) then raise_application_error(-20001, 'somdutt is not there'); end if; if(s1 > 10000) then raise_application_error(-20002, 'somdutt is earing a lot'); end if; update emp set sal=sal+500 where ename='SOMDUTT'; end;

24. Summary • Exception types: • Predefined Oracle Server error • Non-predefined Oracle Server error • User-defined error • Exception trapping • Exception handling: • Trap the exception within the PL/SQL block. • Propagate the exception.

25. Practice Overview • Handling named exceptions • Creating and invoking user-defined exceptions