PLSQL Exception

Exception

Exception :-Exception are used for handle the run-time errors in PL/SQL block.

Types of exception:-Exceptions are divided below category

1) System defined Exception 
                         a) Named Exception
                         b) Un-Named Exception
2) User defined Exception

1) System defined (Named Exception):- These are Oracle defined exceptions, There are about 21 predefined exception, these are 

1)  ACCESS_INTO_NULL                   ORA-06530
2)  CASE_NOT_FOUND                      ORA-06592
3)  COLLECTION_IS_NULL             ORA-06531
4)  CURSOR_ALREADY_OPEN        ORA-06511
5)  DUP_VAL_ON_INDEX                 ORA-00001
6)  INVALID_CURSOR                        ORA-01001
7)  INVALID_NUMBER                       ORA-01722
8)  LOGIN_DENIED                             ORA-01017
9)  NO_DATA_FOUND                        ORA-01403
10) NOT_LOGGED_ON                       ORA-01012
11) PROGRAM_ERROR                       ORA-06501
12) ROWTYPE_MISMATCH             ORA-06504
13) SELF_IS_NULL                             ORA-30625
14) STORAGE_ERROR                       ORA-06500
15) SUBSCRIPT_BEYOND_COUNT  ORA-06533
16) SUBSCRIPT_OUTSIDE_LIMIT ORA-06532
17) SYS_INVALID_ROWID             ORA-01410
18) TIMEOUT_ON_RESOURCE     ORA-00051
19) TOO_MANY_ROWS                    ORA-01422
20) VALUE_ERROR                            ORA-06502
21) ZERO_DIVIDE                               ORA-01476

Examples:-

No_data_found :- This Exception is used to handle the program when query return no row.

Example 1:- Write a program to handle the Exception "no_data_found"

SQL>declare
      vename emp.ename%type;
begin
      select ename into vename from emp where empno=&empno;
      dbms_output.put_line(vename);
end;
/

ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4




To handle this error we use exception "no_data_found" . Below is the example for same

SQL>declare
        vename emp.ename%type;
begin
        select ename into vename from emp where empno=&empno;
        dbms_output.put_line(vename);
exception
        when no_data_found then
        dbms_output.put_line('No such employee exist');
end;
/



Too_many_rows:- This Exception is used to handle the program when query return more than one row
 


Example2:-Write a program to handle the Exception "Too_many_rows"

SQL>declare
       vename emp.ename%type;
begin
      select ename into vename from emp where deptno=&deptno;
     dbms_output.put_line(vename);
end;
/

ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4





To handle this error we can use "too_many_rows" Exception. Below is the example for same


declare
       vename emp.ename%type;
begin
      select ename into vename from emp where deptno=&deptno;
      dbms_output.put_line(vename);
exception
      when too_many_rows then
      dbms_output.put_line('More than one employees in given department number');
end;
/





Example3:-Write a program to handle the exception "no_data_found" and "too_many_rows"

SQL>declare
        vename emp.ename%type;
begin
       select ename into vename from emp where deptno=&deptno;
      dbms_output.put_line(vename);
exception
      when too_many_rows then
      dbms_output.put_line('More than one employees in given department number');
when no_data_found then
      dbms_output.put_line('No employees exist in given department number');
end;
/

declare
       vename emp.ename%type;
begin
      select ename into vename from emp where deptno=&deptno;
      dbms_output.put_line(vename);
exception
       when too_many_rows then
            dbms_output.put_line('More than one employees in given department number');
      when no_data_found then
           dbms_output.put_line('No employees exist in given department number');
      when others then
           dbms_output.put_line('unknown error');
end;
/

Invalid_cursor:- This Exception is used to handle the cursor scope

Example5:-Write a program to handle the Exception "Invalid_cursor"


SQL>declare
cursor c1 is select sal  from emp;
vsal emp.sal%type;
begin
loop
fetch c1 into vsal;
exit when c1%notfound;
dbms_output.put_line(vsal);
end loop;
dbms_output.put_line('Processed records:-'||c1%rowcount);
close c1;
end;
/

ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 6

declare
cursor c1 is select sal  from emp;
vsal emp.sal%type;
begin
loop
fetch c1 into vsal;
exit when c1%notfound;
dbms_output.put_line(vsal);
end loop;
dbms_output.put_line('Processed records:-'||c1%rowcount);
close c1;
exception
when invalid_cursor then
dbms_output.put_line('Cursor is not handled properly');
end;
/

"cursor_already_open" Exception:-

declare
cursor c1 is select sal  from emp;
vsal emp.sal%type;
begin
open c1;
loop
open c1;
fetch c1 into vsal;
exit when c1%notfound;
dbms_output.put_line(vsal);
end loop;
dbms_output.put_line('Processed records:-'||c1%rowcount);
close c1;
exception
when invalid_cursor then
dbms_output.put_line('Cursor is not handled properly');
end;
/


ERROR at line 1:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at line 2
ORA-06512: at line 7

declare
cursor c1 is select sal  from emp;
vsal emp.sal%type;
begin
open c1;
loop
open c1;
fetch c1 into vsal;
exit when c1%notfound;
dbms_output.put_line(vsal);
end loop;
dbms_output.put_line('Processed records:-'||c1%rowcount);
close c1;
exception
when cursor_already_open then
dbms_output.put_line('You are trying to open cursor which is already open');
end;
/

"invalid number" Exception:-

create table abc(name varchar2(10),sal number);

begin
insert into abc values('ABC','100');
end;
/

begin
insert into abc values('XYZ','xyz');
end;
/

ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 2

begin
insert into abc values('XYZ','xyz');
exception
when invalid_number then
dbms_output.put_line('You are using invaid datatyes');
end;
/

"value error" Exception :-




declare
tot number;
begin
tot:='&a'+'&b';
dbms_output.put_line(tot);
end;
/

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4

declare
tot number;
begin
tot:='&a'+'&b';
dbms_output.put_line(tot);
exception
when value_error then
dbms_output.put_line('please use correct data' );
end;
/

User defined exception:- We can declare our own exception and can raise in anywhere in program.When user defined exception raised then exception block called to see the definition of that exception.


declare
a exception;
vsal emp.sal%type;
begin
select sal into vsal from emp where empno=&empno;
if vsal>3000 then
raise a;
else
dbms_output.put_line(vsal);
end if;
exception
when a then
dbms_output.put_line('Salary is too high');
end;
/


declare
a1 exception;
a2 exception;
a3 exception;
a4 exception;
begin
begin
raise a1;
exception
when a1 then
dbms_output.put_line('Exception a1 handled');
raise a3;
end;
exception
when a2 then
dbms_output.put_line('Exception a2 handled');
when a3 then
dbms_output.put_line('Exception a3 handled');
when a4 then
dbms_output.put_line('Exception a4 handled');
end;
/

Error Trapping Functions:-

1) SQL Code:- It returns number
2) SQL Errm:- It return Error message


declare
   name emp.ename%type;
begin
    select ename into name from emp where empno=&eno;
exception
when no_data_found then 
        dbms_output.put_line('SQLCODE: '|| SQLCODE);
        dbms_output.put_line('SQLERRM: '|| SQLERRM);
end;

/


declare
      vsal emp.sal%type;
      str varchar2(500);
      str1 varchar2(100);
begin
     select sal into vsal from emp where empno=&eno;
exception
    when others then
       str:=sqlerrm;
       str1:=sqlcode;
      dbms_output.put_line(str);
      dbms_output.put_line(str1);
end;

/


declare
      cursor c1 is select ename from emp;
      vename emp.ename%type;
      n number;
begin
     open c1;
        loop
           fetch c1 into vename;
           dbms_output.put_line(vename);
           exit when c1%notfound;
        end loop;
    close c1;
      n:=c1%rowcount;
exception
     when invalid_cursor then
    dbms_output.put_line('SQLCODE: '|| SQLCODE);
    dbms_output.put_line('SQLERRM: '|| SQLERRM);
end;

/



Raise Application Error :- If you want to display your own user defined
exception code and exception message then we can use raise_application_error
procedure

Syntax:- raise_application_error(error_number,error_message);

error_number:- it should be between -20000 and -20999
error_message :-It should upto maximum 512 characters

declare
a exception;
vsal emp.sal%type;
begin
select sal into vsal from emp where empno=&empno;
if vsal>3000 then
raise a;
else
dbms_output.put_line(vsal);
end if;
exception
when a then
raise_application_error(-20102,'Salary is too high');
end;
/


declare
vsal emp.sal%type;
begin
select sal into vsal from emp where empno=&empno;
if vsal>3000 then
raise_application_error(-20102,'Salary is too high');
else
dbms_output.put_line(vsal);
end if;
end;
/


Practice Question :-
Q1 :- What is Exception in Oracle ?
Q2 :- What are Exception types in Oracle ? 

No comments:

Post a Comment