|
Exception-Handling |
Top Previous Next |
|
Exception-Handling There are system-defined exceptions and user-defined exceptions. System-defined exceptions: CURSOR_ALREADY_OPEN that is raised then your code attempts to open cursor that is already open
DUP_VAL_ON_INDEX you’ve attempted to insert or update a table with a unique constraint and that unique constraint is violated. Like Progress ** Customer already exists with Cust-Num 1. (132)
INVALID_CURSOR This results from an attempt to fetch or close a cursor that hasn’t been open.
INVALID_NUMBER Datatype conversion was attempted on a literal that failed Like in Progress INTEGER(“123a”) will get ** Invalid character in numeric input a. (76)
NO_DATA_FOUND that is when SELECT returned no rows. Like Progress ** Customer record not on file. (138)
TOO_MANY_ROWS Select was expected to return 1 row and returns more than one.
VALUE_ERROR Usually means convert attempt fails.
ZERO_DIVIDE your code attempted to divide by zero.
Example: EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE(‘Dup_val_on_index exceptioin raised’); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(‘No_data_found exceptioin raised’); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘Some other problem’); END;
User-defined exceptions: Defined in declaration section and used with RAISE statement.
DECLARE CURSOR cur_cust IS SELECT cust_number, start-date, end-date FROM customer ; rec_cust cur_cust%ROWTYPE; exception_dates_incorrect EXCEPTION; BEGIN OPEN cur_cust; LOOP FETCH cur_cust INTO rec_cust; EXIT WHEN cur_cust%NOTFOUND; IF rec_cust.start-date > rec_cust.end_date THEN RAISE exception_dates_incorrect ; END IF; END LOOP; CLOSE cur_cust; EXCEPTION WHEN exception_dates_incorrect THEN INSERT INTO errors ( error_id, message ) VALUES( SEQ_ERROR_ID.NEXTVAL, ‘Start date follows end date’); END;
Here when dates are incorrect, start-date is more than end-date, exception is raised the processing section will terminate and control will pass to exception handling section. In that section the error will be recorded. Here SEQ_ERROR_ID.NEXTVAL is the next value of a sequence, like in Progress. Since we have exception handling the whole procedure will execute with “successfully completed’ message. If we did not have “WHEN exception_dates_incorrect” processing then the general “WHEN OTHERS” will be looked for. If no exception handling will be matched then block will exit with a message indicating that an unhandled exception was raised.
|