PL/SQL Exception

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

Sr.No	Exception
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 shown in below table.

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

Example:-

1) 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".

Code

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

Result

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.

Code

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 Data Found.'); 
end;

2) 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".

Code

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

Result

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.

Code

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".

Code

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;

3) Invalid_cursor :-

This Exception is used to handle the cursor scope.
Example5:-Write a program to handle the Exception "Invalid_cursor".

Code

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;

Result

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

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

Code

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;

4) cursor_already_open :-

This Exception is used to handle the already open cursor .
Example5:-Write a program to handle the Exception "cursor_already_open".

Code

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;

Result

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

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

Code

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;

5) invalid number :-

This Exception is used to handle the invalid number .
Example5:-Write a program to handle the Exception "invalid number".

Code

create table abc(
name varchar2(10),
sal number);
begin
    insert into abc 
    values('ABC','100');
end;
/
begin
    insert into abc 
    values('XYZ','xyz');
end;
/

Result

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

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

Code

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

6) value error :-

This Exception is used to handle the value error .
Example5:-Write a program to handle the Exception "value error".

Code

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

Result

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

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

Code

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.

Code

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;

Below is the another example for same.

Code

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 :-

In pl-sql when run time exception occer then we want to known what kind of error details and error line. To get this details we can we use 'SQLCODE' and 'SQLERRM'.

Sr.No	Keyword	Description
1)	SQLCODE	It returns number
2)	SQLERRM	It return Error message

Code

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;

Below is the another example for same.

Code

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);

Sr.No	Parameter	Description
1)	error_number	it should be between -20000 and -20999.
2)	error_message	It should upto maximum 512 characters.

example for same.

Code

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;

14 March 2021

PL/SQL Exception

Example:-

No comments:

Post a Comment