|
“Character is like
a tree and reputation like its shadow. The shadow is what we
think of it; the tree is the real thing.” Abraham Lincoln
(1809 - 1865), Lincoln's Own Stories |
Read
first then play the video:
PLS007(VIDEO)-EXECPTION
EXCEPTION
Hands-On introduction
In this Hands-On, you
modify the “test_for_loop3” file from previous Hands-On. You need
to add exception to the PL/SQL block.
The PL/SQL EXCEPTION
section is a place that handles your errors that occurs in the
execution time.
In the exception section,
you will add the “others” pre-defined exception to inform a user
if the department number variable contains an invalid number. Then you
add a user defined exception, to check the total number of employees
in a department. You check to see if the total number of employees was
less than 10, then the procedure raises an exception and prints a
message - “We need more good employees.” Then save the file in the
iself directory as “test_exception.sql.”
Go to “MS-DOS.” Change
directory to the iself directory. And login to “SQLPLUS” as “iself/schooling.”
Set the serveroutput option
to on.
SQL> set serveroutput on
Error handling without
EXCEPTION
Open the notepad editor.
Open the “test_for_loop3” file from the iself directory.
Modify the PL/SQL block to
assign “a” to the department number variable.
(Notepad)
DECLARE
v_deptno NUMBER(2);
BEGIN
-- assign deptno 10
v_deptno := ‘a’;
END;
.
Save the file in the iself
directory as “c:_exception.sql”
Go to “SQLPLUS.” Run
the file.
SQL> @test_exception
It will crash. Why? It
crashes since the program didn’t know how to handle the errors.
Error handling with
EXCEPTION
Oracle Defined EXCEPTION
Go back to notepad. Add
exceptions to the PL/SQL block. In the exception section, check if any
exceptions happened then print “Invalid Department number.”
(Notepad)
DECLARE
v_deptno NUMBER(2);
BEGIN
-- assign deptno 10
v_deptno := ‘a’;
EXCEPTION
-- example of PL/SQL
define exception
WHEN invalid_number THEN
DBMS_OUTPUT.PUT_LINE
(‘Invalid deptno: ‘ ||
v_deptno);
WHEN others THEN
DBMS_OUTPUT.PUT_LINE (‘Other
problem.’);
END;
/
Save the file.
Go to “SQLPLUS.” Run
the file again.
SQL> @test_exception
Notice that the PL/SQL
execution will not crash this time. Why?
User Defined EXCEPTION
Go back to notepad. Now,
add the user defined exception to raise an exception if the number of
employees is less than 10. In the declaration section, declare a user
defined exception. In the execution section, check if the total number
of employees is less than 10 then raise the exception. In the
exception section, check if the exception was raised then print “we
need more good employees.”
(Notepad)
>> DECLARE
-- define department
statistics
CURSOR c_ds (p_deptno
dept.deptno%type) IS
SELECT dname, count (*)
ttemp,
sum(sal) ttsal,
avg(sal) avsal
FROM dept d, emp e
WHERE d.deptno = e.deptno
GROUP BY dname;
-- define deptno
variable
v_deptno NUMBER(2);
not_enough_emp
EXCEPTION;
BEGIN
-- assign deptno 10
v_deptno := ‘a’;
-- loop to read cursor
record.
FOR v_ds in c_ds (v_deptno)
LOOP
IF v_ds.ttemp < 10 THEN
raise not_enough_emp;
END IF;
-- insert into dept_stat
INSERT INTO dept_stat
VALUES (v_ds.dname,
v_ds.ttemp,
v_ds.ttsal,
v_ds.avsal);
END LOOP;
-- save the insert
transaction.
COMMIT;
EXCEPTION
-- example of user
define exception
WHEN not_enough_emp
THEN
dbms_output.put_line(‘We
need more employees’);
-- check deptno
WHEN invalid_number THEN
dbms_output.put_line(‘Invalid
deptno: ‘ || v_deptno);
WHEN others THEN
dbsm_output.put_line(‘Other
problem.’);
END;
/
Save the file.
Go to “sqlplus.”
Run the file again.
SQL> @test_exception
Notice that the first
exception terminates the PL/SQL block.
Correct the error
Now, go back to the notepad
editor and correct the error to eliminate the first exception.
(Notepad)
>> DECLARE
-- define department
statistics
CURSOR c_ds (p_deptno
dept.deptno%type) IS
SELECT dname, count (*)
ttemp,
sum(sal) ttsal,
avg(sal) avsal
FROM dept d, emp e
WHERE d.deptno = e.deptno
GROUP BY dname;
-- define deptno
variable
v_deptno NUMBER(2);
not_enough_emp
EXCEPTION;
BEGIN
-- assign deptno 10
v_deptno := 10;
-- loop to read cursor
record.
FOR v_ds in c_ds (v_deptno)
LOOP
IF v_ds.ttemp < 10 THEN
raise not_enough_emp;
END IF;
-- insert into dept_stat
INSERT INTO dept_stat
VALUES (v_ds.dname,
v_ds.ttemp,
v_ds.ttsal,
v_ds.avsal);
END LOOP;
-- save the insert
transaction.
COMMIT;
EXCEPTION
-- example of user
define exception
WHEN not_enough_emp
THEN
dbms_output.put_line(‘We
need more employees’);
-- check deptno
WHEN invalid_number THEN
dbms_output.put_line(‘Invalid
deptno: ‘ || v_deptno);
WHEN others THEN
dbsm_output.put_line(‘Other
problem.’);
END;
/
Save the file.Run the file.
Now, you should not have
any error.
|
“He who has a
thousand friends has not a friend to spare, and he who has one
enemy will meet him everywhere.” Ali ibn-Abi-Talib (602 AD -
661 AD), A Hundred Sayings |
Questions:
Q: What is the EXCEPTION
section in the PL/SQL language?
Q: What do you use the
EXCEPTION section for?
Q: What would be happen if
you don’t define your exception in the PL/SQL procedure?
Q: What is an Oracle
Defined EXCEPTION?
Q: What is a User Defined
EXCEPTION?
Q: What are the differences
between a User Defined and an Oracle defined exceptions?
Q: Modify the previous
PL/SQL block--last assignment in the previous hands-on practice--to
add a user defined exception, to check the total number of employees
in a department. Check if the total number of employees less than 10
then the procedure raises an exception and print a message - “We
need more good employees.”
|