- exception
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
declare
msal rahul_emp.sal%type;
mempno rahul_emp.empno%type;
mename rahul_emp.ename%type;
mjob rahul_emp.job%type;
begin
mjob:=upper('&inpJob');
mempno:= &inpEno;
select ename ,sal into mename,msal from rahul_emp
where job=mjob;
dbms_output.put_line(' The salary of employee '||mename||' is '||msal);
select ename ,sal into mename,msal from rahul_emp
where empno=mempno;
dbms_output.put_line(' The salary of employee '||mename||' is '||msal);
exception
when no_data_found then
dbms_output.put_line('no such employee');
when too_many_rows then
dbms_output.put_line('more than one record');
End;
- exception which occured and error messager
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
declare
msal rahul_emp.sal%type;
mempno rahul_emp.empno%type;
mename rahul_emp.ename%type;
mjob rahul_emp.job%type;
begin
mjob:=upper('&inpJob');
mempno:= &inpEno;
select ename ,sal into mename,msal from rahul_emp
where job=mjob;
dbms_output.put_line(' The salary of employee '||mename||' is '||msal);
select ename ,sal into mename,msal from rahul_emp
where empno=mempno;
dbms_output.put_line(' The salary of employee '||mename||' is '||msal);
exception
when others then
dbms_output.put_line('error code '||sqlcode ||' error message '||sqlerrm);
End;
- exception user defined
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
declare
mcount number;
msal rahul_emp.sal%type;
mename rahul_emp.ename%type;
myException exception;
mjob rahul_emp.job%type;
begin
mjob:=upper('&inpJob');
select count(*) into mcount from rahul_emp
where job=mjob;
if mcount>0 then
dbms_output.put_line('More than 1 emp for same job type ');
else
raise myException;
end if;
exception
when myException then
dbms_output.put_line('My Exception occured due to less count');
dbms_output.put_line('error code '||sqlcode ||' error message '||sqlerrm);
End;
- raise exception (my own)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
declare
cursor cemp is select empno,ename,job,sal,hiredate from rahul_emp where job='&oldInput' Order by ename for update of job;
oldJobTitle rahul_emp.job%type;
newJobTitle rahul_emp.job%type;
myException exception;
updated boolean;
begin
newJobTitle:='&newInput';
updated:=false;
for mrec in cemp
loop
dbms_output.put_line('the original job title of employee ' || mrec.ename || ' is ' || mrec.job);
update rahul_emp
set job=newJobTitle
where current of cemp;
dbms_output.put_line('the revised job title of employee ' || mrec.ename || ' is ' || mrec.job);
updated:=true;
end loop;
if not updated then
raise myException;
end if;
Exception
when myException then
dbms_output.put_line('My Exception occured bec record not found ');
dbms_output.put_line('error code '||sqlcode ||' error message '||sqlerrm);
End;