|Top Previous Next|
There are system-defined exceptions and user-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.
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’);
User-defined exceptions: Defined in declaration section and used with RAISE statement.
CURSOR cur_cust IS
SELECT cust_number, start-date, end-date
FROM customer ;
FETCH cur_cust INTO rec_cust;
EXIT WHEN cur_cust%NOTFOUND;
IF rec_cust.start-date > rec_cust.end_date
RAISE exception_dates_incorrect ;
WHEN exception_dates_incorrect THEN
INSERT INTO errors ( error_id, message )
VALUES( SEQ_ERROR_ID.NEXTVAL, ‘Start date follows end date’);
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.