RDBMS - Exception Handling 1
create a PL/SQL code block that takes empid from the user & displays the empname, doj, bsal of that emp.
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
eid number(5);
enm varchar2(15);
dj date;
bs number(7,2);
BEGIN
/*take user input*/
eid:=&eid;
/*retreive the data*/
SELECT empnm, doj, bsal INTO enm, dj, bs
FROM emptab1
WHERE empid=eid;
/*display*/
DBMS_OUTPUT.PUT_LINE('Employee ID: '||eid);
DBMS_OUTPUT.PUT_LINE('Employee Name: '||enm);
DBMS_OUTPUT.PUT_LINE('Employee DOJ: '||dj);
DBMS_OUTPUT.PUT_LINE('Employee BSAL: '||bs);
END;
/
---------------------------------------------------------------
Exceptions:
Run Time Errors
When Exceptions are detected at Run Time, They crash the PL/SQL code Block
Crash - untimely code block exit
No statement below the error prone statement is executed.
Exception Handling:
the basic idea is to avoid a crash.
PL/SQL code block continues its execution, even if an exception is detected.
just skips the error prone statement / statements.
---------------------------------------------------------------
PL/SQL SYNTAX:
DECLARE
--
BEGIN
--
EXCEPTION
--
END;
------------------------------------------------
EXCEPTION
WHEN exceptionname THEN
action to be carried out
---------------------------------------------------
Built in Exceptions:
1. NO_DATA_FOUND
when the select command does not return any row.
2. TOO_MANY_ROWS
when we fetch less no. of records than what the select statement throws.
3. DUP_VAL_ON_INDEX
when trying to insertduplicate data on a PK col
4. Generic Exception: can catch all remaining exceptions
WHEN OTHERS THEN
action statements
-----------------------------------------------------------
1. NO_DATA_FOUND example
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
eid number(5);
enm varchar2(15);
dj date;
bs number(7,2);
BEGIN
/*take user input*/
eid:=&eid;
/*retreive the data*/
SELECT empnm, doj, bsal INTO enm, dj, bs
FROM emptab1
WHERE empid=eid;
/*display*/
DBMS_OUTPUT.PUT_LINE('Employee ID: '||eid);
DBMS_OUTPUT.PUT_LINE('Employee Name: '||enm);
DBMS_OUTPUT.PUT_LINE('Employee DOJ: '||dj);
DBMS_OUTPUT.PUT_LINE('Employee BSAL: '||bs);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Emp ID '||eid||' not found!');
END;
/
--------------------------------------------------------------
2. TOO_MANY_ROWS example
/*create a PL/SQL code block that takes a name & display all the information of that name*/
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
eid number(5);
enm varchar2(15);
dj date;
bs number(7,2);
BEGIN
/*take user input*/
eid:=&eid;
/*retreive the data*/
SELECT empnm, doj, bsal INTO enm, dj, bs
FROM emptab1
WHERE empid=eid;
/*display*/
DBMS_OUTPUT.PUT_LINE('Employee ID: '||eid);
DBMS_OUTPUT.PUT_LINE('Employee Name: '||enm);
DBMS_OUTPUT.PUT_LINE('Employee DOJ: '||dj);
DBMS_OUTPUT.PUT_LINE('Employee BSAL: '||bs);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Emp name '||enm||' not found!');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Multiple Emp ID '||eid||' found!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unknown Error Detected!');
END;
/
--------------------------------------------------------------
stu1
roll PK
name varchar
insert 1 record
/*Create a Code block to take roll & name from the user & insert the data*/
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
rl number;
nam varchar2(10);
BEGIN
rl:=&rl;
nam:=&nam;
insert into stu1 values(rl,nam);
dbms_output.put_line('1 row created');
EXCEPTION
when DUP_VAL_ON_INDEX then
dbms_output.put_line('roll no: '||rl ||' is used');
END;
/
--------------------------------------------------------------
Enter a rollno & display the per of that roll
SET SERVEROUTPUT ON;
SET ECHO ON;
DECLARE
rl number;
p number(3,1);
BEGIN
/*take user input*/
rl:=&rl;
/*retreive the per of taht roll*/
SELECT per INTO p FROM stutab
WHERE roll=rl;
/*display the per*/
DBMS_OUTPUT.PUT_LINE('Roll no '||rl||' has '||p);
END;
/
This code gives the "no data found" error
Enter a rollno & display the per of that roll. Handle the NO_DATA_FOUND exception.
SET SERVEROUTPUT ON;
SET ECHO ON;
DECLARE
rl number;
p number(3,1);
BEGIN
/*take user input*/
rl:=&rl;
/*retreive the per of taht roll*/
SELECT per INTO p FROM stutab
WHERE roll=rl;
/*display the per*/
DBMS_OUTPUT.PUT_LINE('Roll no '||rl||' has '||p);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Invalid Roll No.');
END;
/
------------------------------------------------------
Enter name & the attendance of a student,
get the student's roll from the stutab,
insert the roll, name & att in the stuatt tab;
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
rl number;
nm varchar2(15);
att number;
BEGIN
/*take user input*/
nm:=&nm;
att:=&att;
/*retrieve roll from stutab*/
select roll INTO rl FROM stutab
where name=nm;
/*insert into the stuatt*/
insert into stuatt
(roll,name,att)
values(rl,nm,att);
END;
/
Enter name & the attendance of a student,
get the student's roll from the stutab,
insert the roll, name & att in the stuatt tab,
handle toomany rows problem
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
rl number;
nm varchar2(15);
att number;
BEGIN
/*take user input*/
nm:=&nm;
att:=&att;
/*retrieve roll from stutab*/
select roll INTO rl FROM stutab
where name=nm;
/*insert into the stuatt*/
insert into stuatt
(roll,name,att)
values(rl,nm,att);
DBMS_OUTPUT.PUT_LINE('Record Inserted!');
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Multiple students with name '||nm||' found!');
END;
/
-----------------------------------------------------------
Practical
--------------------------------------------------------
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 8
SQL> SET SERVEROUTPUT ON;
SQL> SET ECHO ON;
SQL> DECLARE
2 rl number;
3 p number(3,1);
4 BEGIN
5 /*take user input*/
6 rl:=&rl;
7 /*retreive the per of taht roll*/
8 SELECT per INTO p FROM stutab
9 WHERE roll=rl;
10 /*display the per*/
11 DBMS_OUTPUT.PUT_LINE('Roll no '||rl||' has '||p);
12 EXCEPTION
13 WHEN NO_DATA_FOUND THEN
14 DBMS_OUTPUT.PUT_LINE('Invalid Roll No.');
15 END;
16 /
Enter value for rl: 1011
old 6: rl:=&rl;
new 6: rl:=1011;
Roll no 1011 has 72.3
PL/SQL procedure successfully completed.
SQL> SET SERVEROUTPUT ON;
SQL> SET ECHO ON;
SQL> DECLARE
2 rl number;
3 p number(3,1);
4 BEGIN
5 /*take user input*/
6 rl:=&rl;
7 /*retreive the per of taht roll*/
8 SELECT per INTO p FROM stutab
9 WHERE roll=rl;
10 /*display the per*/
11 DBMS_OUTPUT.PUT_LINE('Roll no '||rl||' has '||p);
12 EXCEPTION
13 WHEN NO_DATA_FOUND THEN
14 DBMS_OUTPUT.PUT_LINE('Invalid Roll No.');
15 END;
16 /
Enter value for rl: 1099
old 6: rl:=&rl;
new 6: rl:=1099;
Invalid Roll No.
PL/SQL procedure successfully completed.
SQL> SELECT * FROM stutab;
ROLL NAME DOB M1 M2 PER DEP
---------- --------------- --------- ---------- ---------- ---------- ---
1010 Amit Singh 12-SEP-00 99 88 93.5 CSE
1011 Amer Singh Brar 01-JAN-00 48 96.6 72.3 ECE
1012 Ajmer Singh 11-FEB-00 54.4 66.6 60.5 ECE
1013 John 10-FEB-01 64.4 76.6 70.5 CSE
1014 John 12-MAR-01 74.4 56.6 65.5 CIV
1089 AAAAAA 03-JAN-00 90 90 90 CSE
1025 Simar 45 66 55.5
1090 AWWA 03-JAN-01 49 49 49 CSE
8 rows selected.
SQL> SELECT * FROM stuatt;
ROLL NAME ATT
---------- --------------- ----------
2011 HARRY 348
2012 RAM 248
2512 RAHFH 249
1025 Simar 90
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 rl number;
3 nm varchar2(15);
4 at number;
5 BEGIN
6 /*take user input*/
7 nm:=&nm;
8 at:=&at;
9 /*retrieve roll from stutab*/
10 select roll INTO rl FROM stutab
11 where name=nm;
12 /*insert into the stuatt*/
13 insert into stuatt
14 (roll,name,att)
15 values(rl,nm,at);
16 END;
17 /
Enter value for nm: 'Ajmer Singh'
old 7: nm:=&nm;
new 7: nm:='Ajmer Singh';
Enter value for at: 76
old 8: at:=&at;
new 8: at:=76;
at number;
*
ERROR at line 4:
ORA-06550: line 4, column 2:
PLS-00103: Encountered the symbol "AT" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior
The symbol "begin was inserted before "AT" to continue.
ORA-06550: line 8, column 2:
PLS-00103: Encountered the symbol "AT" when expecting one of the following:
( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited
ORA-06550: line 15, column 15:
PLS-00103: Encountered the symbol "AT" when expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
continue avg count current exists max min prior sq
ORA-06550: line 16, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
( begin case declare end exception exit for goto if loop mod
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 rl number;
3 nm varchar2(15);
4 att number;
5 BEGIN
6 /*take user input*/
7 nm:=&nm;
8 at:=&at;
9 /*retrieve roll from stutab*/
10 select roll INTO rl FROM stutab
11 where name=nm;
12 /*insert into the stuatt*/
13 insert into stuatt
14 (roll,name,att)
15 values(rl,nm,att);
16 END;
17 /
Enter value for nm: 'Ajmer Singh'
old 7: nm:=&nm;
new 7: nm:='Ajmer Singh';
Enter value for at: 76
old 8: at:=&at;
new 8: at:=76;
at:=76;
*
ERROR at line 8:
ORA-06550: line 8, column 2:
PLS-00103: Encountered the symbol "AT" when expecting one of the following:
( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge pipe purge
The symbol "<an identifier> was inserted before "AT" to continue.
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 rl number;
3 nm varchar2(15);
4 att number;
5 BEGIN
6 /*take user input*/
7 nm:=&nm;
8 att:=&att;
9 /*retrieve roll from stutab*/
10 select roll INTO rl FROM stutab
11 where name=nm;
12 /*insert into the stuatt*/
13 insert into stuatt
14 (roll,name,att)
15 values(rl,nm,att);
16 END;
17 /
Enter value for nm: 'Ajmer Singh'
old 7: nm:=&nm;
new 7: nm:='Ajmer Singh';
Enter value for att: 76
old 8: att:=&att;
new 8: att:=76;
PL/SQL procedure successfully completed.
SQL> SELECT * FROM stuatt;
ROLL NAME ATT
---------- --------------- ----------
2011 HARRY 348
2012 RAM 248
2512 RAHFH 249
1025 Simar 90
1012 Ajmer Singh 76
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 rl number;
3 nm varchar2(15);
4 att number;
5 BEGIN
6 /*take user input*/
7 nm:=&nm;
8 att:=&att;
9 /*retrieve roll from stutab*/
10 select roll INTO rl FROM stutab
11 where name=nm;
12 /*insert into the stuatt*/
13 insert into stuatt
14 (roll,name,att)
15 values(rl,nm,att);
16 END;
17 /
Enter value for nm: 'John'
old 7: nm:=&nm;
new 7: nm:='John';
Enter value for att: 24
old 8: att:=&att;
new 8: att:=24;
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 10
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 rl number;
3 nm varchar2(15);
4 att number;
5 BEGIN
6 /*take user input*/
7 nm:=&nm;
8 att:=&att;
9 /*retrieve roll from stutab*/
10 select roll INTO rl FROM stutab
11 where name=nm;
12 /*insert into the stuatt*/
13 insert into stuatt
14 (roll,name,att)
15 values(rl,nm,att);
16 DBMS_OUTPUT.PUT_LINE('Record Inserted!');
17 EXCEPTION
18 WHEN TOO_MANY_ROWS THEN
19 DBMS_OUTPUT.PUT_LINE('Multiple students with name
20
21 '||nm||' found!');
22 END;
23 /
Enter value for nm: 'Amit Singh'
old 7: nm:=&nm;
new 7: nm:='Amit Singh';
Enter value for att: 88
old 8: att:=&att;
new 8: att:=88;
Record Inserted!
PL/SQL procedure successfully completed.
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 rl number;
3 nm varchar2(15);
4 att number;
5 BEGIN
6 /*take user input*/
7 nm:=&nm;
8 att:=&att;
9 /*retrieve roll from stutab*/
10 select roll INTO rl FROM stutab
11 where name=nm;
12 /*insert into the stuatt*/
13 insert into stuatt
14 (roll,name,att)
15 values(rl,nm,att);
16 DBMS_OUTPUT.PUT_LINE('Record Inserted!');
17 EXCEPTION
18 WHEN TOO_MANY_ROWS THEN
19 DBMS_OUTPUT.PUT_LINE('Multiple students with name
20
21 '||nm||' found!');
22 END;
23 /
Enter value for nm: 'John'
old 7: nm:=&nm;
new 7: nm:='John';
Enter value for att: 22
old 8: att:=&att;
new 8: att:=22;
Multiple students with name
John found!
PL/SQL procedure successfully completed.
SQL>
0 Comments