Tag: Pl/Sql

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 reads exception_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