Exceptions
Oracle includes about 20 predefined exceptions (errors) – we can allow Oracle to raise these implicitly.
For errors that don’t fall into the predefined categories – declare in advance and allow oracle to raise an exception.
For problems that are not recognised as an error by Oracle – but still cause some difficulty within your application – declare a User Defined Error and raise it explicitly
i.e IF x >20 then RAISE …
Syntax:
EXCEPTION WHEN exception1 [OR exception2...]] THEN ... [WHEN exception3 [OR exception4...] THEN ...] [WHEN OTHERS THEN ...]
Where exception is the exception_name e.g. WHEN NO_DATA_FOUND… Only one handler is processed before leaving the block.
Trap non-predefined errors by declaring them You can also associate the error no. with a name so that you can write a specific handler.
This is done with the PRAGMA EXCEPION_INIT pragma.
PRAGMA (pseudoinstructions) indicates that an item is a ‘compiler directive’ Running this has no immediate effect but causes all subsequent references to the exception name to be interpreted as the associated Oracle Error.
–
Trapping a non-predefined Oracle server exception
DECLARE
-- name for exception
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;
When an exception occurs you can identify the associated error code/message with two supplied functions SQLCODE and SQLERRM
SQLCODE – Number
SQLERRM – message
An example of using these:
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 t_errors VALUES ( v_error_code, v_error_message); END;
Trapping user-defined exceptions
DECLARE the exception
RAISE the exception
Handle the raised exception
e.g.
DECLARE
e_invalid_product EXCEPTION
BEGIN
update PRODUCT
SET descrip = '&prod_descr'
WHERE prodid = &prodnoumber';
IF SQL%NOTFOUND THEN
RAISE e_invalid_product;
END IF;
COMMIT;
EXCEPTION
WHEN e_invalid_product THEN
DBMS_OUTPUT.PUT_LINE ('INVALID PROD NO');
END;
Propagation of Exception handling in sub blocks
If a sub block does not have a handler for a particular error it will propagate to the enclosing block – where it can be caught by more general exception handlers.
RAISE_APPLICATION_ERROR (error_no, message[,{TRUE|FALSE}]);
This procedure allows user defined error
messages from stored sub programs – call only from stored sub prog.
error_no = a user defined no (between -20000 and -20999)
TRUE = stack errors
FALSE = keep just last
This can either be used in the executable section of code or
the exception section
e.g.
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-2021,
‘manager not a valid employee’);
END;
Standard Exceptions, from the the STANDARD package
|
Oracle Exception Name |
Oracle Error |
Explanation |
| DUP_VAL_ON_INDEX | ORA-00001 | You attempted to create a duplicate value in a field restricted by a unique index. |
| TIMEOUT_ON_RESOURCE | ORA-00051 | A resource timed out, took too long. |
| TRANSACTION_BACKED_OUT | ORA-00061 | The remote portion of a transaction has rolled back. |
| INVALID_CURSOR | ORA-01001 | The cursor does not yet exist. The cursor must be OPENed before any FETCH cursor or CLOSE cursor operation. |
| NOT_LOGGED_ON | ORA-01012 | You are not logged on. |
| LOGIN_DENIED | ORA-01017 | Invalid username/password. |
| NO_DATA_FOUND | ORA-01403 | No data was returned |
| TOO_MANY_ROWS | ORA-01422 | You tried to execute a SELECT INTO statement and more than one row was returned. |
| ZERO_DIVIDE | ORA-01476 | Divide by zero error. |
| INVALID_NUMBER | ORA-01722 | Converting a string to a number was unsuccessful. |
| STORAGE_ERROR | ORA-06500 | Out of memory. |
| PROGRAM_ERROR | ORA-06501 | Generic "Contact Oracle support" message. |
| VALUE_ERROR | ORA-06502 | You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data. |
| ROWTYPE_MISMATCH | ORA-06504 | |
| CURSOR_ALREADY_OPEN | ORA-06511 | The cursor is already open. |
| ACCESS_INTO_NULL | ORA-06530 | |
| COLLECTION_IS_NULL | ORA-06531 |
Named exceptions
An exception can be given a name. In order to create such a named exceptions, two steps are necessary: first, it needs a line that readsexception_name exception. Then, this exception must be connected to a number with the pragma exception_init(exception_name, exc-number). The number must be in the range -20999 through -20000 (these are negative numbers!).
create or replace package exc as
some_exc exception;
pragma exception_init(some_exc, -20005);
procedure raise_exc;
end exc;
/
create or replace package body exc as
procedure raise_exc is begin
raise some_exc;
end raise_exc;
end exc;
/
create or replace package use_exc as
procedure do;
end use_exc;
/
create or replace package body use_exc as
procedure do is begin
exc.raise_exc;
exception
when exc.some_exc then dbms_output.put_line('some_exc');
when others then dbms_output.put_line('others' );
end do;
end use_exc;
/
set serveroutput on
begin
use_exc.do;
end;
/
The output:
some_exc
Reference -http://www.adp-gmbh.ch/ora/plsql/exception.html
