Oracle Exceptions

Exceptions are errors occurred during runtime. Whenever exception is occurred use all appropriate exception name in exception handler in exception section.

Oracle having three types exceptions:
1. Predefined Exception
2. User Defined Exception
3. Unnamed Exception.

PREDEFINED EXCEPTION: Oracle has defined 20+ predefined exception names for regularly occurred runtime errors. Whenever runtime errors occurred, use an appropriate predefined exception name in exception handler under exception section of the PL/SQL block. Some of predefined exceptions are:

CASE_NOT_FOUND
NOT_LOGGED_ON
INVALID_CURSOR
NO_DATA_FOUND
TOO_MANY_ROWS
VALUE_ERROR
INVALID_NUMBER

Syntax:
WHEN Predefined_Exception_Name1 THEN
statements;
WHEN Predefined_Exception_Name2 THEN
statements;
WHEN OTHERS THEN
statements;


Example:

TOO_MANY_ROWS:
In Oracle whenever select into clause try to return multiple records or multiple values at a time. Then Oracle server returns an error ORA-1422: Exact fetch returns more than requested number of rows. To handle this error, Oracle provided TOO_MANY_ROWS exception.

Example:

DECLARE
p-sal NUMBER(10);
BEGIN
SELECT sal into p_sal FROM emp;
dbmsoutput.putline(p_sal);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
dbmsoutput.put_line(‘Not to return multiple values’);
END;
/

USER DEFINED EXCEPTION:
We can also create our own exceptions and also raise whenever necessary. Generally user defined exception is used implementing client business rules.

Process:
Step1: Declare
step2: Raise
step3: Handling

Example:
PL/SQL program to raise an user defined exception on SUNDAY.

DECLARE
p exception;
BEGIN
IF to_char(sysdate,’Dy’)=’SUN’ THEN
raise p;
END IF;
EXCEPTION
WHEN p THEN
dbmsoutput.put_line(‘To day is not working day’);
END;
/

UNNAMED EXCEPTION: In Oracle, if we want to handle other than oracle predefined exception errors, then we are using unnamed method. in this method we are creating our own exception name and associates. THis exception name with appropriate error number using an exception_init(). This functions accepts two parameters.

Syntax:
pragma exception_init(user defined exception name, error number);
This functions is used in declare section of the pl/sql block.
Here pragma is a compiler directive whenever we are using this pragma, Oracle server automatically associates error number with exception name at the time of compilation.

Example:

DECLARE
p exception;
pragma exception_init(p,-1400);
BEGIN
insert into emp(empno, ename) values(null,’Pinnapa’);
EXCEPTION
WHEN p THEN
dbmsoutput.put_line(‘Not to insert null values’);
END;
/

Powered by k2schools