ORACLE PLSQL EXCEPTIONS

EXCEPTIONS:-

What is Exception?

Exception is a error handing program. Which abnormally terminates

Named Exceptions or Pre-defined Exceptions:-

1. DUP_VAL_ON_INDEX   ------  ORA-00001

2. LOGIN_DENIED            -----  ORA-01017

3. NO_DATA_FOUND         ----- ORA-01403

4. NOT_LOGGED_ON         ----- ORA-01012

5. PROGRAM_ERROR         ----- ORA-06501

6. TOO_MANY_ROWS        ----- ORA-01422

7. VALUE_ERROR              ----- ORA-06502

8. ZERO-DIVIDE               ----- ORA-01476

9. INVALID CURSOR         ----- ORA-01001

10. CURSOR ALREADY OPEN --- ORA-06511

What is Pragma?

Pragma is a precompiler directive, which directs the compiler before compilation of the program.

Pragma Autonomous Transaction?

Used to split the whole transaction into two parts parent and child, which run parallely and both have the relation ship

Ex: ITEM ATTCHMENTS CONVERSION

Pragma Exception Init?

Used to associate a user-defined exception with oracle-defined   error.

Ex: Bank Account Min Balance

Mutating Error, Mutating table?

It will occur whenever we try to do DML operations on a table, which is effected by a trigger.  The table, which is affected by trigger, is known as Mutating Error.

RAISE STATEMENT:- Raise an exception only when an error makes it undesirable or impossible to finish processing. we can code a Raise statement for a given exception any where in the scope to that exception.

Ex:- DECLARE

        OUT_OF _STOCK   EXCEPTION;

 NUMBER_ON_HANDS NUMBER(4);

BEGIN

IF NUMBER_ON_HAND < 1 THEN

RAISE OUT_OF _STOCK;

(WE CAN RAISE THE PRE-DEFINED EXCEPTION EXPLICITLY)

END IF;

EXCEPTION

WHEN OUT_OF_STOCK THEN

HANDLE THE ERROR

END;