Author: Debasisdas

In this article I will discuss some useful tips regarding exceptions in oracle, which you may find useful.

Exception:

In PL/SQL, a warning or error condition is called an exception. Exceptions can be internally defined by the runtime system or user defined. A runtime error such as stack overflow or division by zero, stops normal processing and returns control to the operating system. In PL/SQL, exception handling mechanism lets the user trap such conditions, so that it can continue operating in the presence of errors.

Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package. Exception and variable declarations are similar. But remember, an exception is an error condition, not a data item. Unlike variables, exceptions cannot appear in assignment statements or SQL statements. However, the same scope rules apply to variables and exceptions.

Names of common internal exceptions:

Some common internal exceptions have predefined names, such as zero_divide and storage_error. The other internal exceptions can be given names. Unlike internal exceptions, user-defined exceptions must be given names. Below is a list of pre-defined named exceptions

When is an exception raised?

When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions

Scope Rules for PL/SQL Exceptions :

Declaration:

An exception can not be declared twice in the same block. However one can declare the same exception in two different blocks.

Local/Global:

Exceptions declared in a block are considered local to that block and global to all its sub-blocks. Because a block can reference only local or global exceptions, enclosing blocks cannot reference exceptions declared in a sub-block.

Redeclaration:

If you redeclare a global exception in a sub-block, the local declaration prevails. So, the sub-block cannot reference the global exception unless it was declared in a labeled block.

Exceptions in action:

1. Zero_divide:

declare
i int:=&i;
j int:=&j;
k int;
begin
k:=i/j;
dbms_output.put_line(k);
exception
when zero_divide then
raise_application_error(-20002,'cant divide by zero.....!');
when others then
raise_application_error(-20003,'some other error........!');
end;

2. No_data_found:

3. dul_val_on_index:

create procedure insdept(num  number,name varchar2)

is
begin
insert into dept(deptno,dname) values (num,name);
commit;
dbms_output.put_line('One row inserted...!');
exception
when dup_val_on_index then
raise_application_error(-20001,'duplicate entry...!');
when others then
raise_application_error(-20002,'some other error occured...!');
end;

4. too_many_rows:

declare
name varchar2(20);
begin
select ename into name from emp where deptno=&deptnumber;
dbms_output.put_line(name);
exception
when no_data_found then
raise_application_error(-20001,'no such data found...1');
when too_many_rows then
raise_application_error(-20002,'more than one matching record found...!');
when others then
raise_application_error(-20003,'some unexpected error occured...!');
end;

5. invalid_cursor:

In this example, the program will raise the exception because the program tries to access the cursor variable %ROWCOUNT after the cursor is closed.

declare
cursor c1 is select ename from emp where rownum < 11;
name emp.ename%type;
begin
open c1;
loop
fetch c1 into name;
exit when c1%notfound;
dbms_output.put_line(c1%rowcount || '. ' || name);
end loop;
close c1;
dbms_output.put_line(c1%rowcount);
exception
when invalid_cursor then
raise_application_error(-20001,'invalid operation in cursor');
end;

6. Use Defined Exceptions:

declare
 salary emp.sal%type;
 name emp.ename%type;
 no number:=&no;
 greater exception;
 lesser exception;
 begin
 select ename,sal into name,salary from emp where  empNo=no;
 if salary>2000 then
 raise greater;
 else
 raise lesser;
 end if;
 exception
 when greater then
 raise_application_error(-20001,'your salary is more  than 2000');
 when lesser then
 raise_application_error(-20002,'your salary is less   than 2000');
 when no_data_found then
 raise_application_error(-20003,'please   enter a valid  empNo');
 end;

7. User defined exception for oracle defined number:

declare
exp1 exception;
pragma exception_init(exp1,-00001);
begin
insert into dept values(&dno,'&dname','&loc');
dbms_output.put_line('one record inserted');
exception
when exp1 then
dbms_output.put_line('duplicate value');
end;

8. Pragma:

A pragma is a compiler directive that is processed at compile time, not at run time.
The pragma must appear somewhere after the exception declaration in the same declarative section. In below example the user tries to enter null values to a not-null field (empNo field of emp table) which is restricted.

create or replace procedure raiseExp is
empty exception;
pragma exception_init(empty, -01400);
begin
insert into emp(empNo)  values  (null);
commit;
exception
when empty then
dbms_output.put_line('error: trapped fields left null');
when others then
dbms_output.put_line(sqlerrm);
end ;

9. raise_application_error:

The syntax for calling raise_application_error is as under

raise_application_error(error_number, message[, {TRUE | FALSE}]);

where error_number is a negative integer in the range -20000 .. -20999 and message is a character string up to 2048 bytes long.

If the optional third parameter is TRUE, the error is placed on the stack of previous errors. If the parameter is FALSE (the default), the error replaces all previous errors.

raise_application_error is part of package dbms_standard, and as with package standard, you do not need to qualify references to it.

An application can call raise_application_error only from an executing stored subprogram (or method). When called, raise_application_error ends the subprogram and returns a user-defined error number and message to the application. The error number and message can be trapped like any Oracle error.

10. Others:

The exception others can handle all sort of exception.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Great Tips on Table Recovery with RMAN Backup!!
   Great Tips for Test Data Generation in Oracle!!
   Beware of using Oracle RAC with Data Warehouse!!
   The Nuances of regular expressions in Oracle!!