Exception in Database Management System(DBMS).
Exception:
Exception are define as runtime error which may occurs due to semantically problems in a program. If runtime error occurs, program terminate abnormal and we do not know the problem in program . Hence to terminate the program gracefully we handle those error which is known as runtime error handling or exception handling.
when exception handling done in program , program terminates by displaying proper message.
Exception are of following two types:
1. System define exception
2. User define exception
System Define Exception:
Such exception are identified by system and hence known as System define exception. It is again two types:
1.Named System Define Exception
2.Unnamed System Define Exception
Named System Define Exception:
Such exception are well known to the system for which system has assign a proper name. Such exception are define and fired by the system and we need to simply handled them.
Following are the sum of named system exception:
i. Cursor-already-open
ii. Invalid-Cursor
iii. No-Data-Found
iv. To-Many-Rows
v. Zero-Divide
vi. Others
Example:
Write a block to illustrate zero-divide error.
DECLARE
a number:=&a;
b number:=&b;
BEGIN:
DBMS_OUTPUT.PUT_LINE('a/b is '||a/b);
EXCEPTION:
WHEN ZERO-DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('DIVISION BY ZERO IS NOT ALLOWED');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('UNKNOWN EXCEPTION OCCURS');
END;
/
Unnamed System Define Exception:
Such exception are identified by the system but system has not assigned any name. Install system has assign unique codes for them. When such exception occurs, it is fired by the system with code number assigned to its.
The code number fired can be use to same it and do the exception handling. Syntax for handling such exception is given as follows:
DECLARE
exception_name EXCEPTION_TYPE;
..........OTHER DECLARATION..............
PRAGMA
EXCEPTION __INIT(exception_name, -codeNo);
BEGIN
.................BLOCK.......................
......................................................
EXCEPTION
WHEN exception_name THEN
...................................................
...................................................
END;
Example:
Write a block to illustrate Unnamed system define exception,
DECLARE
childrecdel EXCEPTION;
e_id salerep.emp_id%TYPE;
PRAGMA
EXCEPTION __INIT(childrecdel, -z290);
BEGIN
e_id:=&e_id;
DELETE FROM salerep
WHERE emp_id=e_id;
IF (SQL%ROWCOUNT!=0) THEN
DBMS_OUTPUT.PUT_LINE('ROW HAS BEEN DELETED');
END IF;
EXCEPTION
WHEN childrecdel THEN
DBMS_OUTPUT.PUT_LINE('ROW CAN NOT BE DELETED');
END;
User define exception:
For such type of exception system does not know anything and hence it is define with proper name in declare section , it need to be raised in begin section by specify a conditional statement .
After raising the exception it needs to be handle in exception section.
Syntax:
DECLARE
Exception_name EXCEPTION ;
............block.................
BEGIN
........block..........
IF condition THEN
RAISED exception_name
END IF;
EXCEPTION
WHEN exception_name THEN
DBMS_OUTPUT.PUT_LINE('ERROR ');
END;
RAISE_APPLICATION_ERROR(2001,'message');
#For user define reserved code from 20000-20999.
Example:
Write a block illustrate user define exception on handled .
DECLARE
Qty Order.qty%TYPE:=price;
Price product.price %TYPE:=price;
BEGIN
IF(qty*price) >=500;
DBMS_OUTPUT.PUT_LINE('order amount is less then the 500,will ensure a delivery charge of rs 200');
END;
RAISE_APPLICATION_ERROR('200',' ');
Informative
ReplyDelete