ERROR HANDLING
PL/SQL implements error handling with
exceptions and exception handlers. Exceptions can be associated with oracle
errors or with your own user-defined errors. By using exceptions and exception
handlers, you can make your PL/SQL programs robust and able to deal with both
unexpected and expected errors during execution.
ERROR TYPES
Ø Compile-time errors
Ø Runtime errors
Errors that occur during the
compilation phase are detected by the PL/SQL engine and reported back to the
user, we have to correct them.
Runtime errors are detected by the
PL/SQL runtime engine which can programmatically raise and caught by exception
handlers.
Exceptions are designed for run-time
error handling, rather than compile-time error handling.
HANDLING EXCEPTIONS
When exception is raised, control
passes to the exception section of the block. The exception section consists of
handlers for some or all of the exceptions. An exception handler contains the
code that is executed when the error associated with the exception occurs, and
the exception is raised.
Syntax:
EXCEPTION
When exception_name then
Sequence_of_statements;
When
exception_name then
Sequence_of_statements;
When
others then
Sequence_of_statements;
END;
EXCEPTION TYPES
Ø Predefined exceptions
Ø User-defined exceptions
PREDEFINED EXCEPTIONS
Oracle has predefined several
exceptions that corresponds to the most common oracle errors. Like the
predefined types, the identifiers of these exceptions are defined in the STANDARD package. Because of this, they are
already available to the program, it is not necessary to declare them in the declarative section.
Ex1:
DECLARE
a number;
b varchar(2);
v_marks number;
cursor c is select * from student;
type t is varray(3) of varchar(2);
va t := t('a','b');
va1 t;
BEGIN
-
BEGIN
select smarks into
v_marks from student where sno = 50;
EXCEPTION
when no_data_found then
dbms_output.put_line('Invalid student number');
END;
-- CURSOR_ALREADY_OPEN
BEGIN
open c;
open c;
EXCEPTION
when cursor_already_open
then
dbms_output.put_line('Cursor is already opened');
END;
-- INVALID_CURSOR
BEGIN
close c;
open c;
close c;
close c;
EXCEPTION
when invalid_cursor
then
dbms_output.put_line('Cursor is already closed');
END;
-- TOO_MANY_ROWS
BEGIN
select smarks into
v_marks from student where sno > 1;
EXCEPTION
when too_many_rows then
dbms_output.put_line('Too many values are coming to marks variable');
END;
-- ZERO_DIVIDE
BEGIN
a := 5/0;
EXCEPTION
when zero_divide
then
dbms_output.put_line('Divided by zero - invalid operation');
END;
-- VALUE_ERROR
BEGIN
b := 'saketh';
EXCEPTION
when value_error
then
dbms_output.put_line('Invalid string length');
END;
-- INVALID_NUMBER
BEGIN
insert into student
values('a','srinu',100);
EXCEPTION
when invalid_number
then
dbms_output.put_line('Invalid
number');
END;
--
SUBSCRIPT_OUTSIDE_LIMIT
BEGIN
va(4) := 'c';
EXCEPTION
when
subscript_outside_limit then
dbms_output.put_line('Index is greater than the limit');
END;
--
SUBSCRIPT_BEYOND_COUNT
BEGIN
va(3) := 'c';
EXCEPTION
when
subscript_beyond_count then
dbms_output.put_line('Index is greater than the count');
END;
-- COLLECTION_IS_NULL
BEGIN
va1(1) := 'a';
EXCEPTION
when
collection_is_null then
dbms_output.put_line('Collection
is empty');
END;
--
END;
0 comments :
Post a Comment