Handling Exceptions Part F 1 Handling Exceptions with
- Slides: 18
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
- Part part whole
- 미니탭 gage r&r 해석
- Technical description
- Addition symbol
- Parts of back bar
- Part to part ratio definition
- The phase of the moon you see depends on ______.
- Cave of unreported exceptions
- Compararives
- Koch's postulates exceptions
- Question tags and short responses
- Octet rule exceptions
- Obstructive jaundice bilirubin
- Exceptions comparatives
- Present continuous questions
- What is imparfait
- Multiple alleles
- Stark law exceptions
- Pasteurized