Handling Exceptions

1. Handling Exceptions

2. 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.

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

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

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

6. 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.

7. 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

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

9. 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

10. 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

11. 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

12. 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

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

14. SQLCODE SQLERRM Functions for Trapping Exceptions • Example 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;

15. 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

16. 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;

17. 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}]);

18. 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

19. Creating Procedures

20. Overview of Procedures • A procedure is a named PL/SQL block that performs an action. • A procedure can be stored in the database, as a database object, for repeated execution.

21. Syntax for Creating Procedures CREATE [OR REPLACE] PROCEDURE procedure_name (argument1 [mode1] datatype1, argument2 [mode2] datatype2, . . . IS [AS] PL/SQL Block;

22. Procedure IN parameter OUT parameter IN OUT parameter (DECLARE) BEGIN EXCEPTION END; Procedural Parameter Modes Calling environment

23. Parameter Modes for Formal Parameters IN Default Value ispassed into subprogram Formal parameter acts as a constant Actual parametercan be a literal, expression, constant, orinitialized variable OUT Must be specified Returned to calling environment Uninitialized variable Must be a variable IN OUT Must be specified Passed into subprogram; returned to calling environment Initialized variable Must be a variable

24. IN Parameters: Example 7369 v_id SQL> CREATE OR REPLACE PROCEDURE raise_salary 2 (v_id in emp.empno%TYPE) 3 IS 4 BEGIN 5 UPDATE emp 6 SET sal = sal * 1.10 7 WHERE empno = v_id; 8 END raise_salary; 9 / Procedure created. SQL> EXECUTE raise_salary (7369) PL/SQL procedure successfully completed.

25. OUT Parameters: Example Calling environment QUERY_EMP procedure 7654 v_id MARTIN v_name v_salary 1250 v_ comm 1400

26. IN OUT Parameters FORMAT_PHONE procedure Calling environment v_phone_no '(800)633-0575' '(800)633-0575' SQL> CREATE OR REPLACE PROCEDURE format_phone 2 (v_phone_no IN OUT VARCHAR2) 3 IS 4 BEGIN 5 v_phone_no := '(' || SUBSTR(v_phone_no,1,3) || 6 ')' || SUBSTR(v_phone_no,4,3) || 7 '-' || SUBSTR(v_phone_no,7); 8 END format_phone; 9 /

27. Invoking a Procedure from an Anonymous PL/SQL Block DECLARE v_id NUMBER := 7900; BEGIN raise_salary(v_id); --invoke procedure COMMIT; ... END;

28. Invoking a Procedure from a Stored Procedure SQL> CREATE OR REPLACE PROCEDURE process_emps 2 IS 3 CURSOR emp_cursor IS 4 SELECT empno 5 FROM emp; 6 BEGIN 7 FOR emp_rec IN emp_cursor LOOP 8 raise_salary(emp_rec.empno); --invoke procedure 9 END LOOP; 10 COMMIT; 11 END process_emps; 12 /

29. Removing Procedures • Using SQL*Plus: Drop a server-side procedure

30. Removing Server-Side Procedures • Using SQL*Plus: • Syntax • Example DROP PROCEDURE procedure_name SQL> DROP PROCEDURE raise_salary; Procedure dropped.

31. 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.

32. Summary • A procedure is a named PL/SQL block that performs an action. • Use parameters to pass data from the calling environment to the procedure. • Procedures can be invoked from any tool or language that supports PL/SQL. • Procedures can serve as building blocks for an application.