Error Handling in oracle
ERROR
Any departure from the expected behavior of the system or program,
which stops the working of the system is an error.
Types : compile time error
Run time error
EXCEPTION
Any error or problem which one can handle and continue to work normally.
Handling Exception
When exception is raised, control passes to the exception section of the block.
i.e. EXCEPTION
WHEN name_of_exception THEN
Types : Pre Defined Exceptions
User Defined Exceptions
Predefined Exception
*********************
Oracle has predefined several exceptions that correspond to the most common oracle errors.
------------------------------------------------------------------------
Exception Oracle Error SQL Code Value
------------------------------------------------------------------------
ZERO_DIVIDE ORA-01476 -1476
NO_DATA_FOUND ORA-01403 +100
DUP_VAL_ON_INDEX ORA-00001 -1
TOO_MANY_ROWS ORA-01422 -1422
VALUE_ERROR ORA-06502 -6502
CURSOR_ALREADY_OPEN ORA-06511 -6511
OTHERS
------------------------------------------------------------------------
-->-- ZERO_DIVIDE --<--
Your program attempts to divide a number by zero.
DECLARE
v_result NUMBER;
BEGIN
SELECT 23 / 0 INTO v_result FROM dual;
EXCEPTION
WHEN ZERO_DIVIDE THEN
dbms_output.put_line('Divisor is equal to zero');
END;
-->-- NO_DATA_FOUND --<--
Single row SELECT returned no rows or your program referenced a deleted element in a nested table
or an uninitialized element in an associative array (index-by table).
CREATE TABLE test_tb(id NUMBER PRIMARY KEY);
DECLARE
v_id NUMBER;
BEGIN
SELECT id INTO v_id FROM test_tb;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('There is no data inside the table');
END;
-->-- DUP_VAL_ON_INDEX --<--
A program attempted to insert duplicate values in a column that is constrained by a unique index.
INSERT INTO test_tb VALUES (1);
INSERT INTO test_tb VALUES (2);
commit;
BEGIN
INSERT INTO test_tb VALUES (2);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
dbms_output.put_line('Duplicate values are not allowed');
END;
-->-- TOO_MANY_ROWS --<--
Single row SELECT returned multiple rows.
DECLARE
v_id NUMBER;
BEGIN
SELECT id INTO v_id FROM test_tb;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('Query returning more than one row');
END;
DROP TABLE test_tb;
-->-- VALUE_ERROR --<--
An arithmetic, conversion, truncation, or size constraint error occurred.
DECLARE
num1 NUMBER(2);
BEGIN
num1 := 345;
EXCEPTION
WHEN VALUE_ERROR THEN
dbms_output.put_line('check the size of the variable');
END;
-->-- CURSOR_ALREADY_OPEN --<--
A program attempted to open an already opened cursor.
CREATE TABLE emp(id NUMBER, name VARCHAR2(30));
BEGIN
INSERT INTO emp VALUES(1,'Name1');
INSERT INTO emp VALUES(2,'Name2');
INSERT INTO emp VALUES(3,'Name3');
INSERT INTO emp VALUES(4,'Name4');
COMMIT;
END;
SELECT * FROM emp;
DECLARE
cursor emp_c IS
SELECT * FROM emp;
all_data emp%ROWTYPE;
BEGIN
OPEN emp_c;
OPEN emp_c;
NULL;
CLOSE emp_c;
EXCEPTION
WHEN CURSOR_ALREADY_OPEN THEN
dbms_output.put_line('Cursor already opened');
END;
DROP TABLE emp;
-->-- OTHERS --<--
DECLARE
v_result NUMBER;
BEGIN
SELECT 23 / 0 INTO v_result FROM dual;
EXCEPTION
WHEN CURSOR_ALREADY_OPEN THEN
dbms_output.put_line('Cursor already opened');
WHEN OTHERS THEN
dbms_output.put_line('Some other error ' || SQLERRM);
END;
User Defined Exception
**********************
A user-defined exception is an error that is defined by the programmer.
User-defined exceptions are declared in the declarative section of a PL/SQL block. Just like variables,
exceptions have a type EXCEPTION and scope.
DECLARE
v_gender CHAR := '&gender';
gender_ex EXCEPTION;
BEGIN
IF v_gender NOT IN ('M', 'F', 'm', 'f') THEN
RAISE gender_ex;
END IF;
dbms_output.put_line('Gender : '||v_gender);
EXCEPTION
WHEN gender_ex THEN
dbms_output.put_line('Please Enter valid gender');
END;
create table test_insert (id NUMBER, Name VARCHAR2(30));
DECLARE
abort_ex EXCEPTION;
BEGIN
FOR i IN 1..100
LOOP
BEGIN
IF mod(i,10)=0 THEN
RAISE abort_ex;
END IF;
INSERT INTO test_insert VALUES(i, 'Name'||i);
EXCEPTION
WHEN abort_ex THEN
NULL;
END;
END LOOP;
COMMIT;
END;
SELECT * FROM test_insert;
DROP TABLE test_insert;
SQLERRM and SQLCODE
********************
SQLCODE returns the current error code, and SQLERRM returns the current error message text;
For user-defined exception SQLCODE returns 1 and SQLERRM returns “user-defined exception”.
SQLERRM will take only negative value except 100. If any positive value other than 100 returns non-oracle exception.
CREATE TABLE test_tb (id NUMBER);
DECLARE
v_id NUMBER;
BEGIN
SELECT id INTO v_id FROM test_tb;
dbms_output.put_line(v_id);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('SQLERRM : ' || SQLERRM);
dbms_output.put_line('SQLCODE : ' || SQLCODE);
END;
/*sample output*/
SQLERRM : ORA-01403: no data found
SQLCODE : 100
DROP TABLE test_tb;
PRAGMA EXCEPTION_INIT
*********************
Using this you can associate a named exception with a particular oracle error.
This gives you the ability to trap this error specifically, rather than via an OTHERS handler.
Syntax:
PRAGMA EXCEPTION_INIT(exception_name, oracle_error_number);
DECLARE
v_result NUMBER;
PRAGMA EXCEPTION_INIT(Invalid, -1476);
BEGIN
SELECT 453 / 0 INTO v_result FROM dual;
dbms_output.put_line('Result : ' || v_result);
EXCEPTION
WHEN INVALID THEN
dbms_output.put_line('Invalid Exception');
END;
RAISE_APPLICATION_ERROR
************************
You can use this built-in function to create your own error messages, which can be more descriptive than named exceptions.
Error Number :
Oracle Error Range : From -00000 to -19999
User Error Range : From -20000 to -20999
DECLARE
v_gender CHAR := '&gender';
BEGIN
IF v_gender NOT IN ('M', 'F', 'm', 'f') THEN
RAISE_APPLICATION_ERROR(-20003, 'Enter valid gender');
END IF;
dbms_output.put_line('Gender : ' || v_gender);
END;
