Pl/Sql Exception

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

Performance measures in Pl/Sql

Cases should be avoided –1. DO NOT use FUNCTIONS (,TO_NUMBER ,Decode,NVL, TO_CHAR) unnecessarily when a simple join would work
2. DO NOT use ‘SELECT *’ explicitly. Make sure that you select only required columns from the tables.
3. DO NOT use HAVING clause where a WHERE clause would do.
4. DO NOT use ORDER BY clause in a query unless necessarily by logic.
5. DO NOT use UNION and DISTINCT together in a single query
6. DO NOT use CURSOR LOOPS unnecessarily when a simple/single DML/SELECT can serve the purpose
7. DO NOT use DUAL table unnecessarily in programs and queries.

Cases should be followed –
1. Use UNION ALL as far as possible instead of UNION.
2. Use single/direct DML’s/SELECT’s instead of LOOP’s.
3. Use BULK COLLECT & FORALL for looping when the loop is going to get executed multiple time (take > 100 iterations as a base).
4. Use MATERIALIZED VIEWS or GLOBAL TEMPORARY TABLE for queries fetching data across dblinks
5. Use EXISTS in place of IN in the queries.
6. Care should be taken to use the LIMIT clause while using BULK operations in order to limit memory utilization. LIMIT should be set between 500-1000 and reduced or decreased as per performance behavior of the program

References – http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#i48876
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/design.htm#i29012

pimp it