350 likes | 381 Views
Handling Exceptions. 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
E N D
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
Example of an Exception SET SERVEROUTPUT ON DECLARE lname VARCHAR2(15); BEGIN SELECT last_name INTO lname FROM employees WHERE first_name='John'; DBMS_OUTPUT.PUT_LINE ('John''s last name is : ' ||lname); END; /
Example of an Exception SET SERVEROUTPUT ON DECLARE lname VARCHAR2(15); BEGIN SELECT last_name INTO lname FROM employees WHERE first_name='John'; DBMS_OUTPUT.PUT_LINE ('John''s last name is : ' ||lname); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE (' Your select statement retrieved multiple rows. Consider using a cursor.'); END; /
Handling Exceptions with PL/SQL • An exception is a PL/SQL error that is raised during program execution (at run-time) • An exception can be raised: • Implicitly by the Oracle server • Explicitly by the program • An exception can be handled: • By trapping it with a handler • By propagating it to the calling environment
Handling Exceptions Is theexception trapped? Terminate abruptly. No Yes Exception is raised. Execute statementsin the EXCEPTIONsection. Propagate the exception. Terminate gracefully.
Exception Types } • Predefined Oracle server • Non-predefined Oracle server • User-defined Implicitly raised Explicitly raised
Trapping Exceptions • Syntax: EXCEPTION WHEN exception1 [OR exception2 . . .] THEN statement1; statement2; . . . [WHEN exception3 [OR exception4 . . .] THEN statement1; statement2; . . .] [WHEN OTHERS THEN statement1; statement2; . . .]
Guidelines for Trapping Exceptions • The EXCEPTION keyword starts the exception handling section. • Several exception handlers are allowed. • Only one handler is processed before leaving the block. • WHENOTHERS is the last clause.
Trapping Predefined Oracle Server Errors • Reference the predefined name in the exception-handling routine. • Sample predefined exceptions: • NO_DATA_FOUND • TOO_MANY_ROWS • INVALID_CURSOR • ZERO_DIVIDE • CURSOR_ALREADY_OPEN http://www.techonthenet.com/oracle/errors/index.php
An Example • DECLARE • stock_price NUMBER := 9.73; • net_earnings NUMBER := 0; • pe_ratio NUMBER; • BEGIN • pe_ratio := stock_price / net_earnings; • DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio); • EXCEPTION • WHEN ZERO_DIVIDE THEN • DBMS_OUTPUT.PUT_LINE('Company must have had zero earnings.'); • pe_ratio := NULL; • WHEN OTHERS THEN • DBMS_OUTPUT.PUT_LINE('Some other kind of error occurred.'); • pe_ratio := NULL; • END; • /
Declare Trapping Non-Predefined Oracle Server Errors Reference Associate Declarative section EXCEPTION section Name theexception. Use PRAGMA EXCEPTION_INIT. Handle the raised exception.
Non-Predefined Error • To trap Oracle server error number –01400(“cannot insert NULL”): SET SERVEROUTPUT ON DECLARE insert_excep EXCEPTION; PRAGMA EXCEPTION_INIT (insert_excep, -01400); BEGIN INSERT INTO departments (department_id, department_name) VALUES (280, NULL); EXCEPTION WHEN insert_excep THEN DBMS_OUTPUT.PUT_LINE('INSERT OPERATION FAILED'); DBMS_OUTPUT.PUT_LINE(SQLERRM); END;/ 1 2 3
Functions for Trapping Exceptions • SQLCODE: Returns the numeric value for the error code • SQLERRM: Returns the message associated with the error number
Functions for Trapping Exceptions • Example DECLARE error_code NUMBER; error_message VARCHAR2(255); BEGIN ... EXCEPTION ... WHEN OTHERS THEN ROLLBACK; error_code := SQLCODE ; error_message := SQLERRM ; INSERT INTO errors (e_user, e_date, error_code, error_message) VALUES(USER,SYSDATE,error_code, error_message); END; /
Example Declare s_child_exists EXCEPTION; PRAGMA EXCEPTION_INIT(s_child_exists,-2292) Begin delete from s where sno=‘S1’; Exception WHEN s_child_exists THEN dbms_output.put_line(‘Delete the sp_test record first ‘); End;
Trapping User-Defined Exceptions Declare Raise Reference Declarative section Executable section Exception-handling section Name theexception. Explicitly raise the exception by using the RAISE statement. Handle the raised exception.
Trapping User-Defined Exceptions ... ACCEPT deptno PROMPT 'Please enter the department number:' ACCEPT name PROMPT 'Please enter the department name:'DECLARE invalid_department EXCEPTION; name VARCHAR2(20):='&name'; deptno NUMBER :=&deptno; BEGIN UPDATE departments SET department_name = name WHERE department_id = deptno; IF SQL%NOTFOUND THEN RAISE invalid_department; END IF; COMMIT; EXCEPTION WHEN invalid_department THEN DBMS_OUTPUT.PUT_LINE('No such department id.'); END; / 1 2 3
Propagating Exceptions in a Subblock DECLARE . . . no_rows exception; integrity exception; PRAGMA EXCEPTION_INIT (integrity, -2292); BEGIN FOR c_record IN emp_cursor LOOP BEGIN SELECT ... UPDATE ... IF SQL%NOTFOUND THEN RAISE no_rows; END IF; END; Subblocks can handle an exception or pass the exception to the enclosing block. END LOOP; EXCEPTION WHEN integrity THEN ... WHEN no_rows THEN ... END; /
Propagating Exceptions in a Subblock- An Example • DECLARE • past_due EXCEPTION; • acct_num NUMBER; • BEGIN • DECLARE ---------- sub-block begins • past_due EXCEPTION; -- this declaration prevails • acct_num NUMBER; • due_date DATE := SYSDATE - 1; • todays_date DATE := SYSDATE; • BEGIN • IF due_date < todays_date THEN • RAISE past_due; -- this is not handled • END IF; • END; ------------- sub-block ends • EXCEPTION • -- Does not handle raised exception • WHEN past_due THEN • DBMS_OUTPUT.PUT_LINE • ('Handling PAST_DUE exception.'); • WHEN OTHERS THEN • DBMS_OUTPUT.PUT_LINE • ('Could not recognize PAST_DUE_EXCEPTION in this scope.'); • END; • /
Scope of an exception : • DECLARE • v_student_id NUMBER := &sv_student_id; • v_name VARCHAR2(30); • BEGIN • SELECT RTRIM(first_name)||' '||RTRIM(last_name) • INTO v_name • FROM student • WHERE student_id = v_student_id; • DBMS_OUTPUT.PUT_LINE ('Student name is '||v_name); • EXCEPTION • WHEN NO_DATA_FOUND THEN • DBMS_OUTPUT.PUT_LINE ('There is no such student'); • END; • => • The scope of an exception is the portion of the block that is covered by this exception.
Nested Blocks • DECLARE • v_student_id NUMBER := &sv_student_id; • v_name VARCHAR2(30); • v_total NUMBER(1); • -- outer block • BEGIN • SELECT RTRIM(first_name)||' '||RTRIM(last_name) • INTO v_name • FROM student • WHERE student_id = v_student_id; • DBMS_OUTPUT.PUT_LINE ('Student name is '||v_name); • -- inner block • BEGIN • SELECT COUNT(*) • INTO v_total • FROM enrollment • WHERE student_id = v_student_id; • DBMS_OUTPUT.PUT_LINE ('Student is registered for '|| v_total||' course(s)'); • EXCEPTION • WHEN VALUE_ERROR OR INVALID_NUMBER THEN • DBMS_OUTPUT.PUT_LINE ('An error has occurred'); • END; • EXCEPTION • WHEN NO_DATA_FOUND THEN • DBMS_OUTPUT.PUT_LINE ('There is no such student'); • END; • The above example will never raise the exception NO_DATA_FOUND in the inner block. Why
DECLARE • v_student_id NUMBER := &sv_student_id; • v_name VARCHAR2(30); • v_registered CHAR; • -- outer block • BEGIN • SELECT RTRIM(first_name)||' '||RTRIM(last_name) • INTO v_name • FROM student • WHERE student_id = v_student_id; • DBMS_OUTPUT.PUT_LINE ('Student name is '||v_name); • -- inner block • BEGIN • SELECT 'Y' INTO v_registered • FROM enrollment • WHERE student_id = v_student_id; • DBMS_OUTPUT.PUT_LINE ('Student is registered'); • EXCEPTION • WHEN VALUE_ERROR OR INVALID_NUMBER THEN • DBMS_OUTPUT.PUT_LINE ('An error has occurred'); • END; • EXCEPTION • WHEN NO_DATA_FOUND THEN • DBMS_OUTPUT.PUT_LINE ('There is no such student'); • END; • Result : • Enter value for sv_student_id: 284 • old 2: v_student_id NUMBER := &sv_student_id; • new 2: v_student_id NUMBER := 284; • Student name is Salewa Lindeman • There is no such student • PL/SQL procedure successfully completed.
Exception Propagation : • DECLARE • v_test_var CHAR(3):= 'ABCDE'; • BEGIN • DBMS_OUTPUT.PUT_LINE ('This is a test'); • EXCEPTION • WHEN INVALID_NUMBER OR VALUE_ERROR THEN • DBMS_OUTPUT.PUT_LINE ('An error has occurred'); • END; • When executed, this example produces the output shown: • DECLARE • * • ERROR at line 1: • ORA-06502: PL/SQL: numeric or value error: character string buffer too small • ORA-06512: at line 2 • => when a runtime error occurs in the declaration section of the PL/SQL block, • the exception-handling section of this block is not able to catch the error.
--outer block • BEGIN • -- inner block • DECLARE • v_test_var CHAR(3):= 'ABCDE'; • BEGIN • DBMS_OUTPUT.PUT_LINE ('This is a test'); • EXCEPTION • WHEN INVALID_NUMBER OR VALUE_ERROR THEN • DBMS_OUTPUT.PUT_LINE ('An error has occurred in '|| • 'the inner block'); • END; • EXCEPTION • WHEN INVALID_NUMBER OR VALUE_ERROR THEN • DBMS_OUTPUT.PUT_LINE ('An error has occurred in the program'); • END; • When executed, this example produces the output shown: • An error has occurred in the program • PL/SQL procedure successfully completed. • => when a runtime error occurs in the declaration section of the inner block, • the exception immediately propagates to the enclosing (outer) block.
DECLARE • v_test_var CHAR(3) := 'ABC'; • BEGIN • v_test_var := '1234'; • DBMS_OUTPUT.PUT_LINE ('v_test_var: '||v_test_var); • EXCEPTION • WHEN INVALID_NUMBER OR VALUE_ERROR THEN • v_test_var := 'ABCD'; • DBMS_OUTPUT.PUT_LINE ('An error has occurred'); • END; • When executed, this example produces the output shown: • DECLARE • * • ERROR at line 1: • ORA-06502: PL/SQL: numeric or value error: character string buffer too small • ORA-06512: at line 8 • ORA-06502: PL/SQL: numeric or value error: character string buffer too small • => when a runtime error occurs in the exception-handling section • of the PL/SQL block, the exception-handling section of this block is not able to prevent the error.
RAISE_APPLICATION_ERROR Procedure • Syntax: • You can use this procedure to issue user-defined error messages from stored subprograms. • You can report errors to your application and avoid returning unhandled exceptions. raise_application_error (error_number, message[, {TRUE | FALSE}]);
RAISE_APPLICATION_ERROR Procedure • Used in two different places: • Executable section • Exception section • Returns error conditions to the user in a manner consistent with other Oracle server errors
RAISE_APPLICATION_ERROR Procedure Executable section: • Exception section: BEGIN ... DELETE FROM employees WHERE manager_id = v_mgr; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20202, 'This is not a valid manager'); END IF; ... ... EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20201, 'Manager is not a valid employee.'); END;/
Summary • In this lesson, you should have learned how to: • Define PL/SQL exceptions • Add an EXCEPTION section to the PL/SQL block to deal with exceptions at run time • Handle different types of exceptions: • Predefined exceptions • Non-predefined exceptions • User-defined exceptions • Propagate exceptions in nested blocks and call applications