EXCEPTIONS.
Exception
is an abnormal condition that causes the termination of the program in the
middle of the execution.
Exception Handler:
It is a unit of plsql code that oracle
engine raised when an exception is occurred.
When an exception is occurred,oracle engine
identifies it and raises the exception to the exception handler which scans the
plsql program for the occurrence of exception section and if found the
exception handler then checks for the occurrence of the word when followed by a
predefined or user defined exception,which inturn followed by a then keyword and
action associated with that exception.
Exceptions are of 2 types:
1) Predefined exception.
2) User defined exception.
à these can be rasied explicitly by the user.
Some predefined exceptions
examples:
1)
Zero divide
by à Occurs when the divisor is zero.
2)
No_data_found àOccurs when the sql select statement fetching the records into
variables retrieves no rows.
3)
Too_many_rows àOccurs when the sql select statement fetching the
records into variables retrieves more then one row.
NOTE: Exception should be
always placed between begin and end block and exactly before the end.
Syntax: BEGIN
--Any sql Statement.
EXCEPTION
WHEN Exception Name THEN
Any action to be taken
place (like printing a error message)
END;
In the above syntax there
can be many when exception clauses and if we use others exception clause then
it should be placed at last always.
If iam using no_data_found,too_many_rows
and others exception then the seq should be always as per below example
BEGIN
--Any sql Statement.
EXCEPTION
WHEN NO_DATA_FOUND THEN
Any action to be taken
place (like printing a error message);
WHEN
TOO_MANY_ROWS THEN
Any action to be taken
place (like printing a error message);
WHEN OTHERS THEN
Any action to be taken
place (like printing a error message);
END;
But if I write in below way that will
error….
BEGIN
--Any sql Statement.
EXCEPTION
WHEN NO_DATA_FOUND THEN
Any action to be taken
place (like printing a error message);
WHEN OTHERS THEN
Any action to be taken
place (like printing a error message);
WHEN TOO_MANY_ROWS THEN
Any action to be taken
place (like printing a error message);
END;
BEGIN
--Any sql Statement.
EXCEPTION
WHEN OTHERS THEN
Any action to be taken
place (like printing a error message);
WHEN NO_DATA_FOUND THEN
Any action to be taken
place (like printing a error message);
WHEN TOO_MANY_ROWS THEN
Any action to be taken
place (like printing a error message);
END;
SQLERRM: Sql
Error Message…
It is an oracle predefined keyword which
holds oracle error message when any exception raises.
Ex:1
create or replace
procedure xx_emp_dtls(p_empno number)
is
ln_empno emp.empno%type;
ln_ename emp.ename%type;
ln_sal emp.sal%type;
ln_dname dept.dname%type;
ln_loc dept.loc%type;
ln_flag char(1):= 'N';
begin
BEGIN
select empno,ename,sal,dname,loc
into
ln_empno,ln_ename,ln_sal,ln_dname,ln_loc
from emp e,dept d
where empno = p_empno
and e.deptno = d.deptno;
exception
when no_data_found then
dbms_output.put_line('No data exists for the
passed empno'||p_empno);
when too_many_rows then
dbms_output.put_line('too many rows for the
passed');
when others then
dbms_output.put_line('Others exception
raised'||substr(sqlerrm,1,250));
END;
dbms_output.put_line('The
ename is --'||ln_ename);
dbms_output.put_line('The
sal is --'||ln_sal);
dbms_output.put_line('The
empno is --'||ln_empno);
dbms_output.put_line('The
dpartment name is --'||ln_dname);
dbms_output.put_line('The
location is --'||ln_loc);
exception
when others then
dbms_output.put_line('Error in the
xx_emp_dtls proceudre'||substr(sqlerrm,1,250));
end;
/
In the below example we are
using the deptno as a parameter and trying to fetch the records for that deptno
But as per real time one
deptno can always have more then one row which select query could always fail
and go in to exception. In order to handle such kind of these we always need to
print them in a loop.
A select query written in
the plsql block should always retrun a single row values
Ex2: create or replace procedure
xx_emp_dtls(p_deptno number)
is
ln_empno
emp.empno%type;
ln_ename
emp.ename%type;
ln_sal emp.sal%type;
ln_dname
dept.dname%type;
ln_loc dept.loc%type;
ln_flag char(1):= 'N';
begin
BEGIN
select empno,ename,sal,dname,loc
into
ln_empno,ln_ename,ln_sal,ln_dname,ln_loc
from emp e,dept d
where e.deptno = p_deptno
and e.deptno = d.deptno;
exception
when no_data_found then
dbms_output.put_line('No data exists for the
passed empno'||p_deptno);
when too_many_rows then
dbms_output.put_line('too many rows for the
passed');
when others then
dbms_output.put_line('Others exception
raised'||substr(sqlerrm,1,250));
END;
dbms_output.put_line('The
ename is --'||ln_ename);
dbms_output.put_line('The
sal is --'||ln_sal);
dbms_output.put_line('The
empno is --'||ln_empno);
dbms_output.put_line('The
dpartment name is --'||ln_dname);
dbms_output.put_line('The
location is --'||ln_loc);
exception
when others then
dbms_output.put_line('Error in the
xx_emp_dtls proceudre'||substr(sqlerrm,1,250));
end;
/
Print the no of records
exists for the passed deptno and also print the employee dtls for the passed
empno…
create or replace
procedure xx_dtls(p_deptno dept.deptno%type
,p_empno
emp.empno%type)
is
ln_cnt number;
ln_empno emp.empno%type;
ln_ename emp.ename%type;
ln_sal emp.sal%type;
ln_dname dept.dname%type;
ln_loc dept.loc%type;
BEGIN
select count(*)
into
ln_cnt
from
emp
where deptno = p_deptno;
dbms_output.put_line('The no of records is
'||ln_cnt);
-- printing the emp
details
BEGIN
select empno,ename,sal,dname,loc
into
ln_empno,ln_ename,ln_sal,ln_dname,ln_loc
from emp e,dept d
where e.empno = p_empno
and e.deptno = d.deptno;
dbms_output.put_line('The
ename is --'||ln_ename);
dbms_output.put_line('The
sal is --'||ln_sal);
dbms_output.put_line('The
empno is --'||ln_empno);
dbms_output.put_line('The
dpartment name is --'||ln_dname);
dbms_output.put_line('The
location is --'||ln_loc);
exception
when no_data_found then
null;
when others then
dbms_output.put_line('Others exception
raised'||substr(sqlerrm,1,250));
END;
exception
when others then
dbms_output.put_line('Error in the xx_dtls
procedure '||substr(sqlerrm,1,250));
END;
Exercise: modify
the written procedure by handling the exceptions in a right approach
Write a procedure to print the student information and
their total marks and grade.
show student marks,result,grade even if the student
information doesnot exists
by passing input parameter as sno and grade.
Write a procedure to print the student information and
their total marks and grade.
show student marks,result,grade even if the student
information doesnot exists
by passing input parameter as sno and result.
Write a procedure to print the student information and
their total marks and grade.
show student marks,result,grade even if the student
information doesnot exists
by passing input parameter as sno and marks. The student
marks should be greater then marks parameter value.
User defined Exception:
This is a exception which is declared in the declaration section by the user
and rasied explicitly by using raise keyword when ever we require..
Syntax: variable_name EXCEPTION.
Ex: create or replace
procedure xx_dtls(p_deptno dept.deptno%type
,p_empno
emp.empno%type)
is
ln_cnt number;
ln_empno emp.empno%type;
ln_ename emp.ename%type;
ln_sal
emp.sal%type;
ln_dname dept.dname%type;
ln_loc
dept.loc%type;
u_exp
EXCEPTION;
BEGIN
select count(*)
into
ln_cnt
from
emp
where deptno = p_deptno;
dbms_output.put_line('The no of records is '||ln_cnt);
if ln_cnt = 0 then
raise u_exp;
end if;
-- printing the emp details
BEGIN
select empno,ename,sal,dname,loc
into
ln_empno,ln_ename,ln_sal,ln_dname,ln_loc
from emp e,dept d
where e.empno = p_empno
and e.deptno = d.deptno;
dbms_output.put_line('The ename is
--'||ln_ename);
dbms_output.put_line('The sal is --'||ln_sal);
dbms_output.put_line('The empno is
--'||ln_empno);
dbms_output.put_line('The dpartment name is
--'||ln_dname);
dbms_output.put_line('The location is --'||ln_loc);
exception
when no_data_found then
null;
when others then
dbms_output.put_line('Others exception
raised'||substr(sqlerrm,1,250));
END;
exception
when u_exp
then
dbms_output.put_line('user defined exception
rasied');
when others then
dbms_output.put_line('Error in the xx_dtls
procedure '||substr(sqlerrm,1,250));
END;
/
Write a procedure to print
the no of records for the passed deptno and print emp dtails for the passes emp
no. before printing check if the flag value is Y. if it is other then Y or null
or too many rows in the table then raise user defined exception and print a
appropriate message.
Create table ckh_tble(flag
char(1));
create or replace
procedure xx_dtls(p_deptno dept.deptno%type
,p_empno emp.empno%type)
is
ln_cnt number;
ln_empno emp.empno%type;
ln_ename emp.ename%type;
ln_sal
emp.sal%type;
ln_dname dept.dname%type;
ln_loc
dept.loc%type;
u_exp
EXCEPTION;
ln_flag char(1);
BEGIN
begin
select nvl(flag,’N’)
into
ln_flag
from
check_flag
exception
when no_data_found then
ln_flag := 'N' ;
when others then
ln_flag := 'N' ;
end;
if ln_flag = 'N' then
raise u_exp;
end if;
select count(*)
into
ln_cnt
from
emp
where deptno = p_deptno;
dbms_output.put_line('The no of records is
'||ln_cnt);
-- printing the emp details
BEGIN
select empno,ename,sal,dname,loc
into
ln_empno,ln_ename,ln_sal,ln_dname,ln_loc
from emp e,dept d
where
e.empno = p_empno
and e.deptno = d.deptno;
dbms_output.put_line('The ename is
--'||ln_ename);
dbms_output.put_line('The sal is --'||ln_sal);
dbms_output.put_line('The empno is
--'||ln_empno);
dbms_output.put_line('The dpartment name is
--'||ln_dname);
dbms_output.put_line('The location is
--'||ln_loc);
exception
when no_data_found then
null;
when others then
dbms_output.put_line('Others exception
raised'||substr(sqlerrm,1,250));
END;
exception
when u_exp
then
dbms_output.put_line('user defined exception
rasied please check the chk_tble');
when others then
dbms_output.put_line('Error in the xx_dtls
procedure '||substr(sqlerrm,1,250));
END;
/
Insert into table as value
– Y – success for printing
Insert into table as value
– Y,N etc multiple combinations – raise exception
Insert into table as value
– N – raise exception
Insert into table as value
– Null value. – raise exception..
Dynamic message handling
for the above scenarios….
create or replace procedure
xx_dtls(p_deptno dept.deptno%type
,p_empno
emp.empno%type)
is
ln_cnt number;
ln_empno emp.empno%type;
ln_ename emp.ename%type;
ln_sal
emp.sal%type;
ln_dname dept.dname%type;
ln_loc
dept.loc%type;
u_exp
EXCEPTION;
ln_msg
varchar2(1000);
ln_flag char(1);
BEGIN
begin
select nvl(flag,'X')
into
ln_flag
from
check_flag;
exception
when no_data_found then
ln_flag := 'N' ;
ln_msg
:= 'No value exists in the table for the flag';
when others then
ln_flag := 'N' ;
ln_msg := 'Too
many rows or others exception raised';
end;
if ln_flag in ('N','X')
then
if ln_flag = 'X' then
ln_msg := 'Null value exists for the flag
in the check table';
end if;
raise u_exp;
end if;
select count(*)
into
ln_cnt
from
emp
where deptno = p_deptno;
dbms_output.put_line('The no of records is
'||ln_cnt);
-- printing the emp details
BEGIN
select empno,ename,sal,dname,loc
into
ln_empno,ln_ename,ln_sal,ln_dname,ln_loc
from emp e,dept d
where e.empno = p_empno
and e.deptno = d.deptno;
dbms_output.put_line('The ename is
--'||ln_ename);
dbms_output.put_line('The sal is --'||ln_sal);
dbms_output.put_line('The empno is --'||ln_empno);
dbms_output.put_line('The dpartment name is
--'||ln_dname);
dbms_output.put_line('The location is
--'||ln_loc);
exception
when no_data_found then
null;
when others then
dbms_output.put_line('Others exception
raised'||substr(sqlerrm,1,250));
END;
exception
when u_exp
then
dbms_output.put_line(ln_msg);
when others then
dbms_output.put_line('Error in the xx_dtls
procedure '||substr(sqlerrm,1,250));
END;
/
No comments:
Post a Comment