1 / 25

Handling Exceptions

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

jalen
Télécharger la présentation

Handling Exceptions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related