1 / 30

CSIT 313 DB PROGRAMMING

CSIT 313 DB PROGRAMMING. EXCEPTION HANDLING. In PL/SQL, an error condition is called an exception. An exception can be either internally defined (by the run-time system) or user-defined. Examples of System/Oracle exceptions. ORA-22056 (value string is divided by zero) and

bell
Download Presentation

CSIT 313 DB PROGRAMMING

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. CSIT 313 DB PROGRAMMING EXCEPTION HANDLING

  2. In PL/SQL, an error condition is called an exception. • An exception can be either • internally defined (by the run-time system) or • user-defined.

  3. Examples of System/Oracle exceptions • ORA-22056 (value string is divided by zero) and • ORA-27102 (out of memory). • Some common oracle exceptions have predefined names, such as • ZERO_DIVIDE and STORAGE_ERROR. • The other oracle exceptions can be given names at the declaration section.

  4. User Exceptions • You can define your own exceptions in the declarative part of any PL/SQL block, subprogram, or package. • For example, you might define an exception named insufficient_funds to flag overdrawn bank accounts. • User-defined exceptions must be given names at the declaration section.

  5. What happens when an error occurs? • When an error occurs, an exception is raised. • Normal execution stops and control transfers to the exception-handling part of the PL/SQL block or subprogram. • Oracle/System exceptions are raised implicitly (automatically) by the run-time system. • User-defined exceptions must be raised explicitly by RAISE statements

  6. Named system exceptions • Oracle can handle: • CURSOR_ALREADY_OPENED (sqlcode = -6511) • DUP_VAL_ON_INDEX (sqlcode = -1) • INVALID_CURSOR (sqlcode = -1001) • INVALID_NUMBER (sqlcode = -1722) • LOGIN_DENIED (sqlcode = -1017) • NO_DATA_FOUND (sqlcode = +100) • TOO_MANY_ROWS (sqlcode = -1422) • …etc… • These are named in the ‘standard’ package in pl/sql.

  7. To handle these exceptions explicitly: • These exception names do not need to be declared. • To handle them explicitly, put a clause in the exception section: EXCEPTION When DUP_VAL_ON_INDEX dbms_output.put_line(‘record already there’); When OTHER dbms_output.put_line(‘error occurred’); END;

  8. Unnamed system exceptions • These errors are not pre-named, but have a number. • They will be raised automatically by the RDBMS. • The EXCEPTION section handles them in the WHEN OTHER clause. • To name an unnamed error in your application: • Give the error a name using a PRAGMA, or compiler directive, called EXCEPTION_INIT. • PL/SQL or RDBMS raise the error automatically. • Handle the error in a specially written WHEN clause in the exception section.

  9. Unnamed system exceptions • We’ve all seen errors that Oracle throws at us: • ERROR: ORA=12170: TNS: Connect timeout occurred • TNS Listener does not currently know of service requested in connect descriptor • Note: • All of these errors have an error number: • e.g. ORA = 12170 means that the connection timeout occurred. • These errors are RAISED automatically by the system, because they are system errors.

  10. Example DECLARE deadlock_detected EXCEPTION; PRAGMA EXCEPTION_INIT(deadlock_detected, -60); BEGIN ... -- Some operation that causes an ORA-00060 -- error (see locking) EXCEPTION WHEN deadlock_detected THEN -- handle the error END;

  11. Named Programmer-Defined Exceptions • Application-specific exceptions • E.g. • Negative balance in account • Team cannot play against itself • Cannot stock a negative number of items • Programmer can trap these errors and handle them. • To do this: • Name the error • Check for the error and raise it • Handle the error in the EXCEPTION section

  12. Example PROCEDURE calc_annual_sales (company_id_in IN company.company_id%TYPE) IS no_sales_for_company EXCEPTION; BEGIN -- Code here to check the number of sales -- a company has made. If none: raise no_sales_for_company; -- any other code EXCEPTION WHEN no_sales_for_company THEN dbms_output.put_line(company_id||’ has made no sales’); WHEN other THEN rollback work; END;

  13. Example 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; /

  14. Example 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; /

  15. Handling Exceptions with PL/SQL • An exception is an error PL/SQL that is raised during program execution. • 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

  16. Handling Exceptions Program Is theexception trapped? Terminate abruptly no yes Exception raised Execute statementsin the EXCEPTIONsection Propagate the exception Terminate gracefully

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

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

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

  20. 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 • DUP_VAL_ON_INDEX

  21. Declare Trapping Non-Predefined Oracle Server Errors Reference Associate Declarative section EXCEPTION section Name theexception Code PRAGMA EXCEPTION_INIT Handle the raised exception

  22. Non-Predefined Error • 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

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

  24. 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; /

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

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

  27. 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; /

  28. The RAISE_APPLICATION_ERRORProcedure • 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}]);

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

  30. RAISE_APPLICATION_ERROR 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;/

More Related