Exceptions:
In this post we will be learning Exceptions in PL/SQL,
An Exception is a error handling statement. It is used when ever our
program
terminates abnormally by using the exception we can execute the program
from
abnormal termination to normal termination. There are mainly two types of
exceptions
they are given below.
- Predefined Exceptions.
- User defined Exceptions.
.jpg) |
| Exception in PL/SQL |
Predefined
Exceptions: Oracle has predefined
several exceptions that correspond to
the
most common oracle errors. Some predefined exceptions are given below.
LOGIN_DENIED
NO_DATA_FOUND
ZERO_DIVIDE
TOO_MANY_ROWS
STORAGE_ERROR
ROWTYPE_MISMATCH
INVALID_CURSOR
CURSOR_ALREADY_OPEN
Ex:
SQL> declare
cursor
test_cursor is select e.ename, d.deptno,
d.
dname from scott.emp e, scott.dept d
where
e.deptno=20 and d.dname='RESEARCH';
begin
for
i in test_cursor
loop
dbms_output.put_line(i.ename
|| ' ' || i.deptno ||' '|| i.dname);
end
loop;
exception
when
no_data_found then
dbms_output.put_line('NO
DATA FOUND EXCEPTION RAISED');
when
others then
dbms_output.put_line('
An Error Raised ' || sqlerrm);
end
test_cursor;
User
Defined Exceptions: A user defined exception is an error defined by the
programmer.
User defined exceptions are declared in the declarative section of the
PL/SQL
block just like variables.
Syntax:
DECLARE
E_myexception
EXCEPTION;
Ex:
SQL> create or replace procedure razia_proc(v_sal in number) as
v_sl
number;
razia_excep
exception;
begin
if(v_sal
>= 5000)
then
raise
razia_excep;
else
select
sal into v_sl from emp order by deptno;
end
if;
exception
when
razia_excep then
dbms_output.put_line(
'YOU HAVE PERFORMED AN ILLEGAL OPERATION');
end
razia_proc;
