Handling Exceptions Part F 1 Handling Exceptions with

Handling Exceptions Part F 1

Handling Exceptions with PL/SQL What is an exception? Identifier in PL/SQL that is raised during execution n How is it raised? n n An Oracle error occurs. n For example, if the error ORA-01403 occurs when no rows are retrieved from the database in a SELECT statement, then PL/SQL raises the exception NO_DATA_FOUND. n You raise it explicitly. n n The exception being raised may be either user defined or predefined. How do you handle it? 2

Handling Exceptions n Trap the exception DECLARE Exception is raised Exception is trapped BEGIN EXCEPTION END; n If the exception is raised in the executable section of the block and there is no corresponding exception handler, the PL/SQL block terminates with failure and the exception is propagated to the calling environment. 3

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

Predefined Exceptions Most common errors that occur in programs n PL/SQL language: n Assigns exception name n Provides built-in exception handler for each predefined exception n System automatically displays error message informing user of nature of problem n Can create exception handlers to display alternate error messages n 5

Trapping Exceptions n Syntax EXCEPTION WHEN exception 1 [OR exception 2. . . ] THEN statement 1; statement 2; . . . [WHEN exception 3 [OR exception 4. . . ] THEN statement 1; statement 2; . . . ] [WHEN OTHERS THEN statement 1; statement 2; . . . ] 6

Trapping Exceptions Guidelines WHEN OTHERS is the last clause. n EXCEPTION keyword starts exceptionhandling section. n Several exception handlers are allowed. n Only one handler is processed before leaving the block. n You can have at most one OTHERS clause. n Exceptions cannot appear in assignment statements or SQL statements. n 7

Trapping Predefined Oracle Server Errors § Common errors that have been given predefined names n Reference the standard name in the exceptionhandling routine. n Sample predefined exceptions: 8

Predefined Exception n Syntax BEGIN EXCEPTION WHEN NO_DATA_FOUND THEN statement 1; statement 2; WHEN TOO_MANY_ROWS THEN statement 1; WHEN OTHERS THEN statement 1; statement 2; statement 3; END; 9

Undefined Exceptions Less common errors n Do not have predefined names n Must explicitly declare exception in program’s declaration section n Associate new exception with specific Oracle error code n Create exception handler in exception section n n Using same syntax as for predefined exceptions 10

Trapping Non-Predefined Oracle Server Errors Less-common errors that have not been given predefined names Declare Associate Declarative section • Name the exception Reference Exception-handling section • Code the PRAGMA • Handle the EXCEPTION_INIT raised exception 11

Non-Predefined Error n Trap for Oracle Server error number – 2292, an integrity constraint violation. DECLARE e_emps_remaining EXCEPTION; e_emps_remaining PRAGMA EXCEPTION_INIT (( e_emps_remaining, -2292); e_emps_remaining, v_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 12

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

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

User-defined Exceptions Do not raise Oracle runtime error n Require exception handling to n Enforce business rules n Ensure integrity of database n 15

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

User-Defined Exception Example DECLARE EXCEPTION; 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

General Syntax for Declaring, Raising, and Handling a Userdefined Exception 18