------Error Handling and Built-in Exceptions-----
There are two types of errors that can be found in a program: compilation errors and runtime
errors.
a special section in a PL/SQL block handles runtime errors. This is called the exceptionhandling
section.. section, and in it, runtime errors are called exceptions.
The exception-handling section allows programmers to specify what actions should be taken
when a specific exception occurs.
PL/SQL has two types of exceptions: built-in and user-defined.
Handling Errors
DECLARE
V_Num1 NUMBER(2);
V_Num2 NUMBER(2);
V_Num3 NUMBER(5,2);
BEGIN
V_Num1 := &FirstNumber;
V_Num2 := &SecondNumber;
V_Num3 := V_Num1 / V_Num2;
DBMS_OUTPUT.PUT_LINE('The Quotient is : '||V_Num3);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Divide By Zero Not Allowed');
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Size Mismatch');
END;
This example illustrates a runtime error that the compiler cannot detect. For some of the values
entered for the variables v_num1 and v_num2, this example executes successfully. For other
values entered for the variables v_num1 and v_num2, this example cannot execute. As a result,
the runtime error occurs. You will recall that the compiler cannot detect runtime errors. In this
case, a runtime error occurs because the compiler does not know the result of the division of
v_num1 by v_num2. This result can be determined only at runtime. Hence, this error is called
a runtime error.
To handle this type of error in the program, you must add an exception handler. The
exceptionhandling
section has the following structure:
EXCEPTION
WHEN EXCEPTION_NAME THEN
ERROR-PROCESSING STATEMENTS;
Built-in Exceptions
When an error occurs that raises a built-in exception,
the exception is said to be raised implicitly. In other words, if a program breaks an Oracle rule,
control is passed to the exceptionhandling section of the block. At this point, the error-processing
statements are executed. It is important to realize that after the exception-handling section of the
block has executed, the
block terminates. Control does not return to the executable section of the block.
/*
Write a PLSQL Program to handle NO_DATA_FOUND Exception.
*/
DECLARE
V_Empno Emp.Empno%TYPE;
V_Ename Emp.Ename%TYPE;
V_Sal Emp.Sal%TYPE;
V_Comm Emp.Comm%TYPE;
V_TotalSalary Number(7,2);
BEGIN
V_Empno := &Empno;
IF V_Empno IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Sorry !!!!!!! NULL Encountered . ');
RETURN;
ELSE
SELECT Ename ,Sal , Comm INTO V_Ename , V_Sal , V_Comm
FROM Emp
WHERE Empno = V_Empno;
V_TotalSalary := V_Sal + NVL(V_Comm , 0);
DBMS_OUTPUT.PUT_LINE('The Name of the employee is : '|| V_Ename);
DBMS_OUTPUT.PUT_LINE('The Salary of the employee is : '|| V_Sal);
DBMS_OUTPUT.PUT_LINE('The Commission of the employee is : '||NVL(
TO_CHAR(V_Comm),'No Commission' ));
DBMS_OUTPUT.PUT_LINE('The Total Salary of the employee is : '|| V_TotalSalary);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee Does not Exist');
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Size Mismatch');
WHEN INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE('Empno not in a valid format');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
some commonly used predefined exceptions and how they are raised:
NO_DATA_FOUND: This exception is raised when a SELECT INTO statement that makes
no calls to
TOO_MANY_ROWS: This exception is raised when a SELECT INTO statement returns more
than one row.
ZERO_DIVIDE: This exception is raised when a division operation is performed in the
program and a divisor is equal to 0.
LOGIN_DENIED: This exception is raised when a user tries to log in to Oracle with an
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 a conversion or size mismatch error occurs.
DUP_VALUE_ON_INDEX: This exception is raised when a program tries to store a
duplicate value in the column or columns that have a unique index defined on them.
All predefined Oracle errors (exceptions) can be handled with the use of the OTHERS
handler.
Exceptions
Exception Scope- the scope of an exception is the portion of the block that is covered by
this exception.
DECLARE
v_empno NUMBER := &empno_id;
v_name VARCHAR2(30);
v_loc varchar2(30);
-- outer block
BEGIN
SELECT RTRIM(ename)||' '||RTRIM(job)
INTO v_name
FROM emp
WHERE empno = v_empno;
DBMS_OUTPUT.PUT_LINE ('emp name with job is '||v_name);
-- inner block
BEGIN
SELECT locl
INTO v_loc
FROM dept
WHERE deptno = (select deptno from emp where empno=v_empno);
DBMS_OUTPUT.PUT_LINE ('the empl depot no is'||v_loc);
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 empl..');
End;
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 Exceptions
This type of exception is called a user-defined exception because the programmer defines it. As
a result, before the exception can be used, it must be declared. A user-defined exception is
declared in the declaration section of a PL/SQL block:
DECLARE
exception_name EXCEPTION;
After an exception has been declared, the executable statements associated with this exception
are specified in the exception-handling section of the block.
DECLARE
e_invalid_id EXCEPTION;
BEGIN
...
EXCEPTION
WHEN e_invalid_id THEN
DBMS_OUTPUT.PUT_LINE ('An id cannot be negative');
END;
built-in exceptions are raised implicitly. But A user-defined exception must be raised explicitly. In
other words, you need to specify in your program under what circumstances an exception must be
raised:
DECLARE
exception_name EXCEPTION;
BEGIN
...
IF CONDITION THEN
RAISE exception_name;
ELSE
...
END IF;
EXCEPTION
WHEN exception_name THEN
ERROR-PROCESSING STATEMENTS;
END;
Example---
/*
PLSQL Program to demanstrate USER-DEFINED EXCEPTIONs....
*/
DECLARE
V_Num1 NUMBER(5) := ÷nd;
V_Num2 NUMBER(5) := &divisor;
V_Quotient NUMBER(5,2);
One_Divide_Exception EXCEPTION;
BEGIN
IF V_Num1 IS NULL OR V_Num2 IS NULL THEN
DBMS_OUTPUT.PUT_LINE(' Sorry!!!!!!!!!!!!! NULL Encountered...........');
RETURN;
ELSE
IF V_Num2 = 1 THEN
RAISE One_Divide_Exception;
ELSE
NULL;
END IF;
V_Quotient := V_Num1 / V_Num2;
DBMS_OUTPUT.PUT_LINE('The Quotient is : ' || V_Quotient);
END IF;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Divide By Zero not allowed');
WHEN One_Divide_Exception THEN
DBMS_OUTPUT.PUT_LINE('Divide By One not allowed' );
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
It is important for you to note that the RAISE statement should be used in conjunction with an IF
statement.Otherwise, control of the execution is transferred to the exception-handling section of
the block for every execution.
Exception Propagation—
The rules that govern how exceptions are raised in these situations are called exception
propagation.
If an exception is associated with a particular error,
control is passed to the exception-handling section of the block. After the statements associated
with the exception are executed, control is passed to the host environment or to the enclosing
block. If there is no exception handler for this error, the exception is propagated to the enclosing
block (outer block). Then the steps just
described are repeated again. If no exception handler is found, execution of the program halts,
and control is transferred to the host environment.
Next, take a look at a second case: A runtime error occurs in the declaration section of the block.
If there is no outer block, execution of the program halts, and control is passed to the host
environment.
Consider the following script:
DECLARE
v_test_var CHAR(3):= 'ABCDE';
BEGIN
DBMS_OUTPUT.PUT_LINE ('This is a test');
EXCEPTION
WHEN INVALID_NUMBER OR VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;
When executed, this example produces the following output:
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer
too small
ORA-06512: at line 2
when a runtime error occurs in the declaration
section of the PL/SQL block, the exception-handling section of this block cannot catch the error.
--outer block
BEGIN
-- inner block
DECLARE
v_test_var CHAR(3):= 'ABCDE';
BEGIN
DBMS_OUTPUT.PUT_LINE ('This is a test');
EXCEPTION
WHEN INVALID_NUMBER OR VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred in '||
'the inner block');
END;
EXCEPTION
WHEN INVALID_NUMBER OR VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred in the '||
'program');
END;
When executed, this example produces the following output:
An error has occurred in the program
PL/SQL procedure successfully completed.
Therefore, you can conclude that when a runtime error occurs in the declaration section of
the inner block, the exception immediately propagates to the enclosing (outer) block.
/*
PLSQL Program for EXCEPTION PROPAGATION
*/
BEGIN
DECLARE
V_TestVariable CHAR(5) := '&String';
BEGIN
DBMS_OUTPUT.PUT_LINE( ' This is a Test Line . ' );
DBMS_OUTPUT.PUT_LINE( V_TestVariable);
EXCEPTION
WHEN INVALID_NUMBER OR VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE( 'An Error Raised.' );
END;
EXCEPTION
WHEN INVALID_NUMBER OR VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE( 'An Error Raised.' );
END;
/
A runtime error occurs in the exception-handling section of the
block. Just like the previous case, if there is no outer block, execution of the program halts, and
control is passed to the host environment.
DECLARE
v_test_var CHAR(3) := 'ABC';
BEGIN
v_test_var := '1234';
DBMS_OUTPUT.PUT_LINE ('v_test_var: '||v_test_var);
EXCEPTION
WHEN INVALID_NUMBER OR VALUE_ERROR THEN
v_test_var := 'ABCD';
DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;
When executed, this example produces the following output:
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer
too small
ORA-06512: at line 8
ORA-06502: PL/SQL: numeric or value error: character string buffer
too small
when a runtime error occurs in the exception-handling section of the PL/SQL block, the exceptionhandling
section of this block cannot prevent the error
Next, consider an example with nested PL/SQL blocks:
--outer block
BEGIN
-- inner block
DECLARE
v_test_var CHAR(3) := 'ABC';
BEGIN
v_test_var := '1234';
DBMS_OUTPUT.PUT_LINE ('v_test_var: '||v_test_var);
EXCEPTION
WHEN INVALID_NUMBER OR VALUE_ERROR THEN
v_test_var := 'ABCD';
DBMS_OUTPUT.PUT_LINE ('An error has occurred in '||
'the inner block');
END;
EXCEPTION
WHEN INVALID_NUMBER OR VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred in the '||
'program');
END;
When executed, this example produces the following output:
An error has occurred in the program
PL/SQL procedure successfully completed.
Therefore, you can conclude that when a runtime error occurs in the exception-handling section of
the inner block, the exception immediately propagates to the enclosing block.
When a PL/SQL block is not enclosed by another block, control is transferred to the host
environment,
and the program cannot complete successfully.
RERAISING AN EXCEPTION
On some occasions you may want to be able to stop your program if a certain type of error
occurs. In other words, you may want to handle an exception in the inner block and then pass
it to the outer block. This process is called reraising an exception.
The following example-----
-- outer block
DECLARE
e_exception EXCEPTION;
BEGIN
-- inner block
BEGIN
RAISE e_exception;
EXCEPTION
WHEN e_exception THEN
RAISE;
END;
EXCEPTION
WHEN e_exception THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;
In this example, the exception e_exception is declared in the outer block. Then it is raised
in the inner block. As a result, control is transferred to the exception-handling section of the
inner block. The RAISE statement in the exception-handling section of the block causes the
exception to propagate to the exception-handling section of the outer block.
Notice that when the RAISE statement is used in the exception-handling section of the inner
block, it is not followed by the exception name.
When run, this example produces the following output:
The error has occurred
PL/SQL procedure successfully completed.
/*
PLSQL Program to demonstrate RE-RAISING An EXCEPTION.
*/
DECLARE
E_Exception EXCEPTION;
BEGIN
RAISE E_Exception;
EXCEPTION
WHEN E_Exception THEN
RAISE;
END;
EXCEPTION
WHEN E_Exception THEN
RAISE;
END
RAISE_APPLICATION_ERROR
RAISE_APPLICATION_ERROR is a special built-in procedure provided by Oracle. It allows
programmers to create meaningful error messages for a specific application. The RAISE_
APPLICATION_ERROR procedure works with user-defined exceptions; its syntax is
RAISE_APPLICATION_ERROR(error_number, error_message);
or
RAISE_APPLICATION_ERROR(error_number, error_message, keep_errors);
error_number is a number that a programmer associates with a specific error message. It can be
any number between –20,999 and –20,000. error_message is the text of the error; it can contain
up to
The second form of RAISE_APPLICATION_ERROR contains one additional parameter:
keep_errors, which is an optional Boolean parameter. If keep_errors is set to TRUE, the
new error is added to the list of errors that have been raised already. This list of errors is called
the error stack. If keep_errors is set to FALSE, the new error replaces the error stack that has
been raised already. The default value for the parameter keep_errors is FALSE.
It is important to note that the RAISE_APPLICATION_ERROR procedure works with
unnamed user-defined exceptions. It associates the number of the error with the text of the error.
/*
PLSQL Program to demanstrate USER-DEFINED EXCEPTIONs....
Using RAISE _APPLICATION_ERROR....
*/
DECLARE
V_Num1 NUMBER(5) := ÷nd;
V_Num2 NUMBER(5) := &divisor;
V_Quotient NUMBER(5,2);
BEGIN
IF V_Num1 IS NULL OR V_Num2 IS NULL THEN
DBMS_OUTPUT.PUT_LINE(' Sorry!!!!!!!!!!!!! NULL Encountered...........');
RETURN;
ELSE
IF V_Num2 = 1 THEN
RAISE_APPLICATION_ERROR
(
-20001 ,
'One_Divide_Exception'
);
ELSE
NULL;
END IF;
V_Quotient := V_Num1 / V_Num2;
DBMS_OUTPUT.PUT_LINE('The Quotient is : ' || V_Quotient);
END IF;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Divide By Zero not allowed');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
EXCEPTION_INIT Pragma
Often your programs need to handle an Oracle error that has a particular number
associated
with it, but no name by which it can be referenced. As a result, you are unable to write a handler
to trap this error. In a case like this, you can use a construct called a pragma. A pragma is a
special instruction to the PL/SQL compiler. It is important to note that pragmas are processed
at the time of the compilation. The EXCEPTION_INIT pragma allows you to associate an Oracle
error number with the name of a user-defined error. After you associate an error name with an
Oracle error number, you can reference the error and write a handler for it.
After you associate an error name with an
Oracle error number, you can reference the error and write a handler for it.
The EXCEPTION_INIT pragma appears in the declaration section of a block as shown:
DECLARE
exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT(exception_name, error_code);
The EXCEPTION_INIT pragma has two parameters: exception_
name and error_code. exception_name is the name of your exception, and error_code
is the number of the Oracle error you want to associate with your exception. Consider the
following:
/*
PLSQL Program to demonstrate PRAGMA EXCEPTION_INIT........
*/
DECLARE
V_Deptno Dept.Deptno%TYPE := &Deptno;
Exception_Deptno EXCEPTION;
PRAGMA EXCEPTION_INIT( Exception_Deptno , -2292 );
BEGIN
DELETE FROM Dept
WHERE Deptno = V_Deptno;
EXCEPTION
WHEN Exception_Deptno THEN
DBMS_OUTPUT.PUT_LINE('Child Rows Found');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
Notice that this error has Oracle error number ORA-02292 assigned to it, but it does not have
a name. As a result, you need to associate this error number with a user-defined exception so
that you can handle this error in the script.
Therefore, the EXCEPTION_INIT pragma improves
the readability of your error-handling interface. If the need arises, you can use multiple
EXCEPTION_INIT pragmas in your program.
SQLCODE and SQLERRM
all Oracle errors can be trapped with the help of the OTHERS exception handler,
PLSQL Program to demonstrate SQLCODE and SQLERRM.
*/
DECLARE
V_Num1 NUMBER(5);
V_Num2 NUMBER(5);
V_Quotient NUMBER(5,2);
BEGIN
V_Num1 := &Dividend;
V_Num2 := &Divisor;
IF V_Num1 IS NULL OR V_Num2 IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Sorry!!!!!! NULL Encountered..........');
RETURN;
ELSE
V_Quotient := V_Num1 / V_Num2;
DBMS_OUTPUT.PUT_LINE('Quotient is :' || V_Quotient);
END IF;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1476 THEN
DBMS_OUTPUT.PUT_LINE('Divide by Zero not allowed here.');
ELSIF SQLCODE = -6502 THEN
DBMS_OUTPUT.PUT_LINE('Value Error.');
END IF;
END;
you cannot always know all the possible runtime errors that may occur
when a
program is running. Therefore, it is a good practice to have the OTHERS exception handler in
your script. To improve the error-handling interface of your program, Oracle gives you two
built-in functions, SQLCODE and SQLERRM, used with the OTHERS exception handler. 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.
/*
PLSQL Program to demonstrate Function for trapping EXCEPTIONS.
*/
DECLARE
V_ErrorCode NUMBER(6);
V_ErrorMessage VARCHAR2(200);
BEGIN
INSERT INTO Dept VALUES
( &DepartmentNumber , '&DepartmentName' , '&DepartmentLocation');
EXCEPTION
WHEN OTHERS THEN
V_ErrorCode := SQLCODE;
V_ErrorMessage := SUBSTR(SQLERRM , 1 , 200);
DBMS_OUTPUT.PUT_LINE(' The error code traced is ' || V_ErrorCode);
DBMS_OUTPUT.PUT_LINE(' The Error Message traced is '|| V_ErrorMessage );
END;
/
the SQLCODE function returns a negative number for an error number. However,
there are a few exceptions:
When SQLCODE is referenced outside the exception section, it returns 0 for the error code.
The value of 0 means successful completion.
When SQLCODE is used with the user-defined exception, it returns +1 for the error code.
SQLCODE returns a value of 100 when the NO_DATA_FOUND exception is raised.
The SQLERRM function accepts an error number as a parameter, and it returns an error message
corresponding to the error number.
No comments:
Post a Comment