Exceptions In Oracle

 Exceptions:
 - Errors in Pl/sql block are termed as exceptions.
 - Error Handling solutions are provided in exception     block
 - 3 Types
 1. Pre-defined exceptions
    - Defined by oracle
    - Activated by oracle automatically
    - Solution provided by User

 2. User-defined Exception    - Defined by user
    - Activated by user using "Raise" Stmt
    - solution provided by user.

 3. Non Pre-defined Exceptions
    (Undefined Exceptions)
    - Defined by user
    - Activated automatically by constraints
    - Solution provided by user
    - used to handle constraint voilation errors in              pl/sql.

        Declare
        .........
        Begin
        ............
        ............
        Exception
        ...............
        ..............
        End;


 * Using Exceptions:
 Pl/sql Block handles different types of errors

 declare
 veno emp.empno%type := &employ; 
 vname emp.ename%type;
 vsal emp.sal%type;
 -- Declaring user defined Exception
 salary_missing Exception;
 begin
 select ename,sal into vname,vsal from emp
 where empno = veno;
 if vsal is null then
 -- activating user defined exception
 raise salary_missing;
 else
 vsal := vsal + vsal *.25;
 update emp set sal = vsal where empno = veno;
 dbms_output.put_line(veno||'  '||vname||'     '||vsal);
 commit;
 end if;
 Exception
  when no_data_found then
 dbms_output.put_line(' No such employ exists with  employ code  '||veno);
 when too_many_rows then
 dbms_output.put_line(' More than one employ      found with same empno  '||veno); 
 when salary_missing then
 dbms_output.put_line(' Employ has null salary -      providing default pay ');
 update emp set sal = 15000 where empno = veno;
 commit;
 when others then
 dbms_output.put_line(' Internal error occured ');
 dbms_output.put_line(sqlcode||'   '||sqlerrm); 
 end;
---------------------------------------------------------------
 Pre-defined exceptions:
 * No_data_found:  Automatically activated       whenever select statement fails to retrieve data       into variables.

 * Too_many_rows:  Automatically activated    whenever select  statement retrieves more than    one row into variables.

 * value_error:  Automatically activated whenever     data types are not matching.
 Ex:  declare
       a number(3);
       b date;
       x number(2);
        begin
          select ename,sal into a,b from emp
          where empno = 7369;

 * zero_divide:  Automatically activated whenever   invalid arithmatic operation is performed (n/0).

 * Dup_val_on_index:  Automatically activated   whenever duplicate values are inserted into unique   index column.

 * storage_error:  Automatically activated whenever    there is lack of memory in server.

 * Invalid_cursor:  Automatically activated         whenever user fetch the rows from cursor      without opening the cursor.

 * cursor_already_open:  Automatically activated    whenever user opens the cursor which is already      in open state.

 * Time_out_on_resources:  Automatically activated    whenever user performs an infinite loop process.

 * Transaction_backed_out: Automatically activated  whenever there is an communication problem with   server.

 * Login_denied: Automatically activated whenever
  user name or password are not valid.

 * Others:  General Exception
  Used to handle any pre-defined exception.
  It will not support Non pre-defined/user defined     exceptions.
 Pseudo columns activated by others Exception

 i) SQLCODE : Holds the currently raised error no
 ii) SQLERRM : Holds the currently raised error          message
 It must be last exception in exception block.
---------------------------------------------------------------  

* Pl/sql block using non pre-defined exception:
 * Pl/sql block handling Foreign key constraint error
 declare
 vdept number(2) := &dept;
 employ_exists exception;
 pragma exception_init(employ_exists,-2292);
 begin
 delete from dept where deptno = vdept;
 commit;
 exception
 when employ_exists then
 dbms_output.put_line(' Employees are still working   - cannot remove Department ');
 end;
---------------------------------------------------------------

* Exception_init(Exception name,Error no);
   Built_in sub program used to provide exception        name to the constraint voilation error no.

* Pragma - It is an special instruction to Pl/sql to       execute sub program in declaration section.

 dept        emp
 -----        -----
 deptno (PK)       empno
        deptno (FK)

 Constraint voilation Error No's:
 -1400    ---> not null
 - 00001 ---> unique
 - 2290    ---> check
 -2291    ---> parent not exists ( Ref )
 -2292    ---> depending child records exists ( Ref )
---------------------------------------------------------------
Ex: 3
 create table stud_info1
 (roll number(4), sname varchar2(20),
 course varchar2(20)
 check (course in('ORACLE','UNIX','DWH','D6I')),
 fee number(5));

 insert into stud_info1      values(101,'SRIRAM','JAVA',2500);

*  Pl/Sql Block handling check constraint Error
 declare
 Invalid_course exception;
 pragma exception_init(Invalid_course,-2290);
 begin
 insert into stud_info1     values(101,'SRIRAM','JAVA',2500);
 commit;
 exception
 when Invalid_course then
 dbms_output.put_line
 (' Student Course must be "ORACLE" or "UNIX" or     "DWH"  or  "D6I" only ');
 end;
---------------------------------------------------------------
 Ex: Using Nested blocks,cursors with Lock and               Exceptions.
 create table itemmast
 ( itno number(4), name varchar2(20),
 qoh number(5), rate number(5));

 insert into itemmast values(&1,'&2',&3,&4);

 create table ittran
 ( itno number(4),
 tran_type char(1),  [ I -> ISSUES(-),
                                R -> RECIEPTS(+) ]
 qty number(5), updt char(1));  

 insert into ittran values(&1,'&2',&3,'&4');

 * Pl/sql block updating the Transactions to Master     table:
  declare
  -- cursor with lock
 cursor tran is select itno,tran_type,qty from ittran
 where upper(updt) = 'N' for update;
 Cursor item is select * from itemmast;
 vqoh number(5);
 begin
 for i in tran loop
 BEGIN  -- Nested Block
 select qoh into vqoh from itemmast
 where itno = i.itno;
 EXCEPTION
 when no_data_found then
 dbms_output.put_line(' No such item exists with      itno  ' ||i.itno);
 END;
 if i.tran_type = 'R' then
 vqoh := vqoh + i.qty;
 elsif i.tran_type = 'I' then
 vqoh := vqoh - i.qty;
 end if;
 update itemmast set qoh = vqoh
 where itno =  i.itno;
 update ittran set updt = 'Y' where current of tran;
 end loop;                          
 commit;
 dbms_output.put_line(' STOCK REPORT ');
 for k in item loop
 dbms_output.put_line(K.itno||'  '||K.name
 ||'      '||K.qoh);
 end loop;
 exception
 when others then
 dbms_output.put_line(sqlcode||'   '||sqlerrm);
 end;
---------------------------------------------------------------  

* For Update -- Locks the rows retrieved into       cursor to prevent other users accessing same rows   at same time.
 * " Where current of  " - Clause (8.0)
 - used to locate the current row manipulated by         cursor
 - To use this cursor must be locked.
---------------------------------------------------------------
 Exercise:
 Bankmaster        Transaction
 ---------------        --------------
 accno --- 101        accno --- 101
 acc_type (S,C,R) --- S    tran_type (W,D) --- W
 name --- RAM        tran_date --- sysdate
 curr_bal --- 10000         amt --- 4000                                                    updt --- N

 *  write a pl/sql program to update all the                  transactions to Bankmaster Table.
 *  Check for Minimum Balance while performing           Withdrawals with user friendly messages.
---------------------------------------------------------------