When a SELECT INTO
statement calls a group function, such as
COUNT, the result set
is never empty. When used in the
SELECT INTO statement
against the STUDENT table, function
COUNT will return 0
for the value of student ID 123. Hence,
any SELECT statement
that calls any group function will never
raise the NO_DATA_FOUND exception.
• TOO_MANY_ROWS This exception is raised when a
SELECT
INTO statement returns
more than one row. By definition, a
SELECT INTO can return
only single row. If a SELECT INTO
statement returns more
than one row, the definition of the
SELECT INTO statement
is violated. This causes the
TOO_MANY_ROWS
exception to be raised.
For example, you issue
a SELECT INTO statement against the
STUDENT table for a
specific zip code. There is a big chance
that this SELECT
statement will return more than one row be-cause
many students can live
in the same zip code area.
• ZERO_DIVIDE This exception is raised when a
division opera-tion
is performed in the
program and a divisor is equal to zero.
An example in the
previous lab of this chapter illustrates how
this exception is
raised.
• LOGIN_DENIED This exception is raised when a user
is trying
to login on to Oracle
with invalid username or password.
• PROGRAM_ERROR This exception is raised when a
PL/SQL
program has an
internal problem.
• VALUE_ERROR This exception is raised when
conversion or
size mismatch error
occurs. For example, you select customer’s
last name into a
variable that has been defined as VAR-CHAR2(
5). If customer’s last
name contains more than five char-acters,
VALUE_ERROR exception
is raised.
• DUP_VALUE_ON_INDEX This exception is raised when a
pro-gram
tries to store a
duplicate value in the column or columns
that have a unique
index defined on them. For example, you
are trying to insert a
record into the SECTION table for the
course number “25,”
section 1. If a record for the given course
and section numbers
already exists in the SECTION table,
DUP_VAL_ON_INDEX
exception is raised because these
columns have a unique
index defined on them.
So far, you have seen
examples of exception-handling sections that have
particular exceptions,
such as NO_DATA_FOUND or ZERO_DIVIDE. How-ever,
you cannot always
predict beforehand what exception might be
raised by your PL/SQL
block. In cases like this, there is a special exception
handler called OTHERS.
All predefined Oracle errors (exceptions) can be
handled with the help
of the OTHERS handler.
EXAMPLE:
--built_in_exceptions.sql
SET SERVEROUTPUT ON;
DECLARE
v_Cust_no NUMBER := &sv_student_id;
v_enrolled VARCHAR2(3) := 'NO';
BEGIN
SELECT 'YES'
INTO v_enrolled
FROM bookings
WHERE Cust_no = v_Cust_no;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('This customer rented from us');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE
('This customer rented from us more than once!');
END;
EXCEPTION SCOPE
FOR EXAMPLE
DECLARE
v_cust_no NUMBER
:= &sv_cust_no;
v_cust_name
VARCHAR2(50);
v_total NUMBER(1);
-- outer block
BEGIN
SELECT
RTRIM(cust_name)
INTO v_cust_name
FROM customers
WHERE cust_no =
v_cust_no ;
DBMS_OUTPUT.PUT_LINE('Customer
name is '|| v_cust_name);
-- inner block
BEGIN
SELECT COUNT(*)
INTO v_total
FROM bookings
WHERE cust_no = v_cust_no;
DBMS_OUTPUT.PUT_LINE
('Customer has been booked for '||v_total||
' rentals(s)');
EXCEPTION
WHEN VALUE_ERROR OR INVALID_NUMBER
THEN
DBMS_OUTPUT.PUT_LINE
('An error has occurred');
END;
EXCEPTION
WHEN
NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE('There
is no such customer');
END;
When VALUE_ERROR or
INVALID_NUMBER error occurs in the inner
block, the exception
is raised if the number of bookings were more than a 2 digit number.
It is important that
you realize that exceptions VALUE_ERROR and
INVALID_NUMBER have
been defined for the inner block only. Therefore,
they can be raised in
the inner block only. If one of these errors occurs in
the outer block, this
program will be unable to terminate successfully.
On the other hand, the
exception NO_DATA_FOUND has been defined
in the outer block;
therefore, it is global to the inner block. This version
of the example will
never raise the exception NO_DATA_FOUND in the
inner block.
It is important to note that if you define
an exception in a block, it is
local to that block. However, it is global
to any blocks enclosed by that
block. In other words, in the case of
nested blocks, any exception defined
in the outer
block becomes global to its inner blocks.
USER-DEFINED
Example
DECLARE
e_test_exception EXCEPTION; --user_defined_exception.sql
BEGIN
DBMS_OUTPUT.PUT_LINE('Exception has not been raised');
RAISE e_test_exception;
-- this DBMS_OUTPUT.PUT_LINE statement -- will never execute
DBMS_OUTPUT.PUT_LINE('Exception has been raised');
EXCEPTION
WHEN e_test_exception THEN
DBMS_OUTPUT.PUT_LINE('An error has occurred');
END;
To improve the
error-handling
interface of your
program, Oracle provides you with two built-in func-tions,
SQLCODE and SQLERRM, used with the OTHERS
exception han-dler.
The SQLCODE function
returns the Oracle error number, and the
SQLERRM function
returns the error message. The maximum length of a
message
returned by the SQLERRM function is 512 bytes.
Example:
DECLARE
v_post_code
VARCHAR2(5) := '&sv_post_code';
v_town
VARCHAR2(15);
v_err_code
NUMBER;
v_err_msg
VARCHAR2(200);
BEGIN
SELECT
town
INTO
v_town
FROM
customers
WHERE
post_code = v_post_code;
DBMS_OUTPUT.PUT_LINE(v_town||'.
');
EXCEPTION
WHEN
OTHERS
THEN
v_err_code
:= SQLCODE;
v_err_msg
:= SUBSTR(SQLERRM, 1, 200);
DBMS_OUTPUT.PUT_LINE
('Error
code: '||v_err_code);
DBMS_OUTPUT.PUT_LINE
('Error
message: '||v_err_msg);
END;