PL/SQL Exception Handling - I

PL/SQL Exception Handling - I

What is an exception :
In PL/SQL, an error condition is called an exception.

What happens if an exception raises?
When an exception is raised, the pl/sql program leads to abnormal termination which leads to
improper result sets.

To handle situations when an exception raises, we use exception handlers in
a planned manner.By that we can terminate program smoothly and also displaying and handling
the errors raised.

We have 2 types of exceptions
1. Internally Defined
2. User Defined

Internally Defined Exceptions:

An internal exception is raised automatically if your PL/SQL program violates a
database rule or exceeds a system-dependent limit. PL/SQL predefined some common ORA-n errors as internal exceptions.

Some common internal exceptions have predefined names,
for ex: NO_DATA_FOUND etc.,
Other predefined exceptions have error numbers but not given any names.
These can be associated with names using pragma EXCEPTION_INIT.






Example to handle internally predefined exception associated with
predefined name.

--take a table and insert few rows

create table XX_TEST
(
ref_id number(4),
Name varchar2(20),
mobile number(10)
);


begin
insert into xx_test values (1001,'Oracle',12345);
insert into xx_test values (1001,'Jave',54321);
commit;
end;
/

select * from xx_test;

create or replace procedure exp_hand_proc
as
v_mobile xx_test.mobile%type;
v_mobile1 xx_test.mobile%type;
begin
select mobile into v_mobile
from xx_test
where name='Oracle';
dbms_output.put_line('Mobile no :'||v_mobile);
select mobile into v_mobile1
from xx_test
where name='dotnet';
dbms_output.put_line('Mobile no :'||v_mobile1);
dbms_output.put_line('End of procedure');
exception
when NO_DATA_FOUND then
dbms_output.put_line('Ora Error :'||sqlerrm);
dbms_output.put_line('No data exists exception raised');
When OTHERS then
dbms_output.put_line(SQLCODE||'-'||SQLERRM);
end;
/

-- Run procedure and see how output displayed.
begin
exp_hand_proc;
end;
/

If an internal excpetion is raised, and if doesnot know the name of predefined excpetion
then use OTHERS handler in the exception block. And dispaly
error message and error number associated with excpetion using
sqlcode and sqlerrm.

Few others predefined excpetions are
DUP_VAL_ON_INDEX,INVALID_CURSOR,INVALID_NUMBER
LOGIN_DENIED,NOT_LOGGED_ON,PROGRAM_ERROR,
ROWTYPE_MISMATCH,SELF_IS_NULL,STORAGE_ERROR,
SUBSCRIPT_BEYOND_COUNT,SUBSCRIPT_OUTSIDE_LIMIT,
SYS_INVALID_ROWID,TIMEOUT_ON_RESOURCE,TOO_MANY_ROWS,
VALUE_ERROR,ZERO_DIVIDE

Will post examples on user defined excpetions in my next article.




Exception Handling -II Pragma Exception_init

Thanks!