1 / 18

CHAPTER 5 EXCEPTION HANDLING

CHAPTER 5 EXCEPTION HANDLING. PL/SQL BLOCK STRUCTURE. PL/SQL block has the following structure: DECLARE Declaration statements BEGIN Executable statements EXCEPTION Exception-handling statements END ;. EXCEPTION-HANDLING SECTION.

Download Presentation

CHAPTER 5 EXCEPTION HANDLING

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. CHAPTER 5EXCEPTION HANDLING

  2. PL/SQL BLOCK STRUCTURE PL/SQL block has the following structure: DECLARE Declaration statements BEGIN Executable statements EXCEPTION Exception-handling statements END ;

  3. EXCEPTION-HANDLING SECTION The exception-handling section is the last section of the PL/SQL block. This section contains statements that are executed when a runtime error occurs within a block. Runtime errors occur while the program is running and cannot be detected by the PL/SQL compiler. EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE (‘ There is no student with student id 123 ’); END;

  4. System exceptions Named Unnamed User-defined exceptions Types of exceptions

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

  6. 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 Then dbms_output.put_line(‘record already there’); END; To handle these exceptions explicitly:

  7. Example DECLARE v_num1 integer := &sv_num1; v_num2 integer := &sv_num2; v_result number; BEGIN v_result := v_num1 / v_num2; DBMS_OUTPUT.PUT_LINE (‘v_result: ’|| v_result); EXCEPTION WHEN ZERO_DIVIDETHEN DBMS_OUTPUT.PUT_LINE (‘A number cannot be divided by zero.’); END;

  8. EXAMPLE DECLARE v_student_id char(5) := &sv_student_id; v_first_name VARCHAR2(35); v_last_name VARCHAR2(35); BEGIN SELECT first, last INTO v_first_name, v_last_name FROM student WHERE studentid = v_student_id; DBMS_OUTPUT.PUT_LINE ('Student name: '||v_first_name||' '||v_last_name); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('There is no such student'); END;

  9. So far, you have seen examples of the programs able to handle a single exception only. • For example, a PL/SQL contains an exception-handler with a single exception ZERO_DIVIDE. • However, many times in the PL/SQL block you need to handle different exceptions. • Moreover, often you need to specify different actions that must be taken when a particular exception is raised. HANDLING DIFFERENT EXCEPTIONS

  10. DECLARE v_student_id NUMBER := &sv_student_id; v_enrolled VARCHAR2(3) := 'NO'; BEGIN DBMS_OUTPUT.PUT_LINE (‘Check if the student is enrolled’); SELECT ‘YES’ INTO v_enrolled FROM enrollment WHERE student_id = v_student_id; DBMS_OUTPUT.PUT_LINE (‘The student is enrolled into one course’); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(‘The student is not enrolled’); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE (‘The student is enrolled into many courses’); END; • This example contains two exceptions in the single exception handling section. • The first exception, NO_DATA_FOUND, will be raised if there are no records in the ENROLLMENT table for a particular student. • The second exception, TOO_MANY_ROWS, will be raised if a particular student is enrolled into more than one course.

  11. You have seen examples of exception-handling sections that have particular exceptions, such as NO_DATA_FOUND or ZERO_DIVIDE. • However, you cannot always predict beforehand what exception might be raised by your PL/SQL block. • In cases like this, there is a special exception handler called OTHERS. • All predefined Oracle errors (exceptions) can be handled with the help of the OTHERS handler. OTHERS Handler

  12. When run, this example produces the following output: • Enter value for sv_instructor_id: 100 • old 2: v_instructor_id NUMBER := &sv_instructor_id; • new 2: v_instructor_id NUMBER := 100; • An error has occurred • PL/SQL procedure successfully completed. • This demonstrates not only the use of the OTHERS exception handler, but also a bad programming practice. • The exception OTHERS has been raised because there is no record in the INSTRUCTOR table for instructor ID 100. Example DECLARE v_instructor_id NUMBER := &sv_instructor_id; v_instructor_name VARCHAR2(50); BEGIN SELECT first_name||' '||last_name INTO v_instructor_name FROM instructor WHERE instructor_id = v_instructor_id; DBMS_OUTPUT.PUT_LINE (‘Instructor name is’ ||v_instructor_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘An error has occurred’); END;

  13. 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 OTHERS 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. Unnamed system exceptions

  14. 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. Unnamed system exceptions

  15. DECLARE deadlock_detectedEXCEPTION; 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; Example

  16. User Defined Exceptions This type of exception is called a user-defined exception because it is defined by the programmer. Before the exception can be used, it must be declared. A user-defined exception is declared in the declaration part of a PL/SQL block as shown below: DECLARE exception_name EXCEPTION; Once an exception has been declared, the executable statements associated with this exception are specified in the exception-handling section of the block. The format of the exception-handling section is the same as for named exceptions.

  17. Example DECLARE e_invalid_id EXCEPTION; BEGIN … EXCEPTION WHEN e_invalid_id THEN DBMS_OUTPUT.PUT_LINE ('An id cannot be negative'); END;

  18. DECLARE exception_name EXCEPTION; BEGIN … IF CONDITION THEN RAISE exception_name; ELSE … END IF; EXCEPTION WHEN exception_name THEN ERROR-PROCESSING STATEMENTS; END; Raising Exception A user-defined exception must be raised explicitly. In other words, you need to specify in your program under which circumstances an exception must be raised as shown :

More Related