ex
BEGIN
-- first insert
BEGIN
Insert into myTab(ID,NAME) values (1,'name1');
EXCEPTION
when DUP_VAL_ON_INDEX then
null; -- do something or ignore the error
END;
-- second insert
BEGIN
Insert into myTab(ID,NAME) values (2,'name2');
EXCEPTION
when DUP_VAL_ON_INDEX then
null; -- do something or ignore the error
END;
END;
There are two types of exceptions defined in PL/SQL
- User defined exception.
- System defined exceptions.
System defined exceptions:
System-defined exceptions are further divided into two categories:
- Named system exceptions.
- Unnamed system exceptions.
- Named system exceptions: They have a predefined name by the system like ACCESS_INTO_NULL, DUP_VAL_ON_INDEX, LOGIN_DENIED etc.....
- NO_DATA_FOUND: It is raised WHEN a SELECT INTO statement returns no rows
TOO_MANY_ROWS:It is raised WHEN a SELECT INTO statement returns more than one row
ZERO_DIVIDE = raises exception WHEN dividing with zero
Unnamed system exceptions:Oracle doesn’t provide name for some system exceptions called unnamed system exceptions.These exceptions don’t occur frequently.These exceptions have two parts code and an associated message.
The way to handle to these exceptions is to assign name to them using Pragma EXCEPTION_INIT
Syntax:
The way to handle to these exceptions is to assign name to them using Pragma EXCEPTION_INIT
Syntax:
PRAGMA EXCEPTION_INIT(exception_name, -error_number);
error_number are pre-defined and have negative integer range from -20000 to -20999.
Example:
DECLARE
exp exception;
pragma exception_init (exp, -20015);
n int:=10;
BEGIN
.......
RAISE exp;
........
EXCEPTION
WHEN exp THEN
dbms_output.put_line('aaaa');
END;
Scope rules in exception handling:
We can’t DECLARE an exception twice but we can DECLARE the same exception in two dIFferent blocks.
Exceptions DECLAREd inside a block are local to that block and globalto all its sub-blocks.
As a block can reference only local or global exceptions, enclosing blocks cannot reference exceptions DECLAREd in a sub-block.
If we reDECLARE a global exception in a sub-block, the local declaration prevails i.e. the scope of local is more.
Example:
DECLARE
AAAAA EXCEPTION;
age NUMBER:=16;
BEGIN
-- sub-block BEGINs
DECLARE
-- this declaration prevails
AAAAA EXCEPTION;
age NUMBER:=22;
BEGIN
IF age > 16 THEN
RAISE AAAAA ; /* this is not handled*/
END IF;
END;
-- sub-block ends
EXCEPTION
-- Does not handle raised exception
WHEN AAAAA THEN
DBMS_OUTPUT.PUT_LINE
('Handling AAAAA exception.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE
('Could not recognize exception AAAAA in this scope.');
END;
------------------------
LIST of Ex
LIST of Ex
Brief descriptions of the predefined exceptions follow:
No comments:
Post a Comment