Exception Handling

System Defined Exceptions & User Defined Exceptions in Oracle

1. Database Lab
SwapnaliPawar
Predefined & User Defined
Exceptions
Swapnali Pawar

2. •Anexceptionisanerrorconditionduringaprogram
execution.Anerrororawarningeventiscalledanexception.
•Anyabnormalconditionorsayeventthatinterruptsthe
normalflowofyourprogram’sinstructionsatruntimeisan
exception.Orinsimplewordsyoucansayanexceptionisa
runtimeerror.
•Exceptionsaredesignedforruntimeerrorhandlingrather
thancompiletimeerrorhandling.Errorsthatoccurduring
compilationphasearedetectedbythePL/SQLcompilerand
reportedbacktotheuser.
What is an Exception?
Swapnali Pawar

3. Exceptions
Swapnali Pawar

4. Swapnali Pawar
Exception Handling

5. There are two types of PL/SQL exceptions in Oracle
database.
1.PreDefined/ System-defined
Exceptions
2. User-Defined Exceptions
Types of Exceptions
Swapnali Pawar

6. •System-defined exceptions are defined and maintained
implicitly by the Oracle server. These exceptions are mainly
defined in theOracle STANDARD package. Whenever an
exception occurs inside the program. The Oracle server
matches and identifies the appropriate exception from the
available set of exceptions.
•System defined exceptions majorly have a negative error code
and error message. These errors have a short name which is
used with the exception handler.
1. System-Defined Exceptions
Swapnali Pawar

7. UnlikeSystem-DefineException,User-DefineExceptionsare
raisedexplicitlyinthebodyofthePL/SQLblocki.emore
specificallyinsidetheBEGIN-ENDsection)usingtheRAISE
Statement.
2. User-Define Exceptions
Swapnali Pawar

8. •Ifourcodedoesnothaveanexceptionhandling,theneach
timeweexecuteastatement,wemustverifyerrorsin
execution
.
•Ifweavoidexceptionhandlinginourcode,theactualerrors
getmissedwhichgivesrisetosomeothererrors.
•Theexceptionhandlingallowsskippingmultipleverifications
inthecode.
•Itprovidesbetterreadabilityofthecodebyisolatingtheerror
handlersinthecode
Advantages Of Exception Handling
Swapnali Pawar

9. 1. Pre-Defined Exception
•system defined exceptions are thrown by
default.
•Some of the popular System defined exceptions
areout of memoryanddivision by
zero,having names like STORAGE_ERROR and
ZERO_DIVIDErespectively.
Swapnali Pawar

10. Exception
Oracle
Error
SQL
CODE
Description
ACCESS_INTO_NULL ORA -06530-6530This exception is raised if a null object is naturally assigned a
value.
CASE_NOT_FOUND ORA -06592-6592This exception is raised if none of the options in the WHEN
clause is chosen and there is no existence of an ELSE clause.
COLLECTION_IS_NULLORA -06531-6531This exception is raised when the code tries to apply collection
methods except EXISTS to a nested table or varraywhich is not
initialized. It can also be raised if our code tries to assign values
to a nested table or varraywhich is not initialized.
DUP_VAL_ON_INDEX ORA -00001-1 This exception is raised if duplicate values are tried to be stored
in a column that is constrained by a unique index.
CURSOR_ALREADY_OP
EN
ORA -06511-6511This exception is raised if our code tries to open an already open
cursor.
INVALID_CURSOR ORA -01001-1001This exception is raised if we try to do some operations on
cursors which are not permitted. For example, attempting to
close an already closed cursor.
INVALID_NUMBER ORA -01722-1722This exception is raised if the conversion to a character string to
a number does not pass as the string is representing an invalid
number.
Pre-Defined Exception
Swapnali Pawar

11. Exception
Oracle
Error
SQL
CODE
Description
SYS_INVALID_ROWID ORA-01410-1410This exception is raised if the
conversion to a character string to a
universal row id does not pass as the
character string is representing an
invalid row id.
TIMEOUT_ON_RESOUR
CE
ORA-00051-51 This exception is raised if Oracle is
waiting for a resource.
VALUE_ERROR ORA-06502-6502This exception is raised if a
mathematical, conversion, truncation
error happens in our program.
ZERO_DIVIDE ORA-01476-1476This exception is raised if we try to
divide a number by 0.
Swapnali Pawar

12. 1. Pre-Defined Exception
Exceptions Defined by Oracle i.eSystem Defined
Exceptions-
1 . Zero_divide
2 . value_error
3 . Invalid_Number
4 . N0_Data_Found
5 . Too_Many_Rows
6 . Dup_Val_On_Index
7 . Cursor_Already_Open
8 . Invalid_Cursor
Swapnali Pawar

13. This Exception occurs if any number is divided by
Zero .
Eg-
a := &a; # 16
b:= &b; # 0
c:= a/b; # Occurs Divide_Zero
Exception
1.Zero_divide
Swapnali Pawar

14. 2. Value_error
If Value Size exceeds or if data type is mismatched then
this exception occurs
A Number(3):=50000; # Exceeding 4 digit so
Value_ErrorOccurs
B Number(4):=‘Swapnali’ # Data Type Missmatch
Value_ErrorOccurs
Swapnali Pawar

15. 3. Invalid_Number
This exception occurs if invalid numeric arithmetic
operation is performed
Eg-
‘Swapnali’+10#InvalidArithmetic
Operation
Swapnali Pawar

16. 4. No_Data_Found
This Exception occurs if data is not found in Table
N := &roll_no;
Select stud_name# No_Data_FoundException
From Student
Where roll_no=N;
Swapnali Pawar

17. 5. Too_Many_Rows
This exception occurs if select statement try to fetch
more than 1 record.
Eno:= &deptno;
Select enameinto EmpName
From employee
Where deptno=Eno;
If 10 records are there for select statement then select statement
fetches all 10 names & are assigned to EmpNamebut variable
EmpNametakes only one at a time so too_many_rowsexception
occurs
Swapnali Pawar

18. 6. Dup_Val_On_Index
This exception Occurs if we try to insert duplicate
value in primary key column.
Eg-
Create table MyFriends(id number primary key , name varchar(30));
Insert into MyFriendsid values(101);
Insert into MyFriendsid values(101);
# 10 already exist in table so Dup_Val_On_Indexexception occurs
Swapnali Pawar

19. 7. Cursor_Already_Open
Ifyoutrytoopencursorwhichisalreadyopen
withoutclosingitthenCursor_Already_Open
exceptionoccurs.
Openc1;
Openc1;
C1isalreadyopenthusCursor_Already_Openexceptionoccurs.
Swapnali Pawar

20. 8. Invalid_Cursor
If you try to open cursor which is no declared then
Invalid_Cursorexception occurs.
Open C1;
Cursor C1 is not declared & you try to open it Thus
Invalid_Cursorexception occurs.
Swapnali Pawar

21. •User-defined exceptions are declared in a package, subprogram,
or within the declaration section of the PL/SQL block of code
and should be assigned names.
•Once an exception occurs, the natural flow of execution is
halted,and then the execution points to the exception section
of the PL/SQL code.
•User-defined ones have to be thrown explicitly by the RAISE
keyword.
•Thus the exception handling helps to deal with the errors that
are encountered during the run time execution and not while
compiling the program.
User-Defined Exception
Swapnali Pawar

22. 1.Declare a variable of exception data type –
This variable is going to take the entire burden on its shoulders.
2.Raise the Exception –
This is the part where you tell the compiler about the condition
which will trigger the exception.
3.Handle the exception –
This is the last section where you specify what will happen
when the error which you raised will trigger
Steps for Exception Handling
Swapnali Pawar

23. User-Defined Exception
Thedeveloperscanbuildtheirownexceptionsandusethemfor
handlingerrors.Theycanbecreatedinthedeclarationpartofa
subprogramandcanbeaccessedonlyinsidethatsubprogram.
Anexceptionthatiscreatedatthepackagelevelcanbeused
wheneverthepackageisaccessed.Auser-definedexceptioncanbe
raisedbyusingtheRAISEkeyword.
Syntax-
Declare
Exception_nameException;
Here,theexception_nisthenameoftheexceptionthatweare
raising.Thuswecandeclareanexceptionbygivinganamefollowed
bytheEXCEPTIONkeyword.Anexceptioncanbedeclaredina
similarmannerlikevariablesaredeclared.However,anexceptionis
anunexpectedconditionandnotadataitem.
Swapnali Pawar

24. Syntax for Exception Handling
DECLARE
<< declaration section >>
BEGIN
<<Block of executable code>>
IF condition THEN
RAISE exception_n;
END IF;
EXCEPTION
WHEN excp1 THEN
<< excp1 handling block >>
WHEN excp2THEN
<< excp2 handling block >>
........
WHEN others THEN
<< excp2 handling block>>
END;
The default exception is carried out withWHEN others THEN.
Swapnali Pawar

25. 1. Raise Statement
2. Raise_Application_ErrorStatement
User-Defined Exception
When predefined exceptions don’t satisfy our requirement then
we define our own exception
Swapnali Pawar

26. 1.Using Raise Statement-
Exception is raised using name.Thisstatement is
used if you want to raise exception & also want
to handle it.
2. Using Raise_Application_Error
Statement-
Exception is raised using code.Thisstatement is
used if you want to raise exception but don’t
want to handle it.
Swapnali Pawar
User-Defined Exception

27. Swapnali Pawar
1.To find sum of two
number:
set serveroutputon;
declare
a int;
b int;
c int;
begin
a:=&a;
b:=&b;
c:=a+b;
dbms_output.put_line('sum
of a and b'||c);
end;
2.To find greatest number among
two:
set serveroutputon
declare
a int;
b int;
c int;
begin
a:=&a;
b:=&b;
if(a>b)
then
dbms_output.put_line('a is greater');
else
dbms_output.put_line('b is greater');
end if;
end;

28. Declare
a number(2);
b number(2);
c number(4);
one_divideexception;
Begin
a:= &a;
b:= &b;
if b=1 then
raise one_divide;
end if ;
c:= a/b;
dbms_Output.put_line(c);
Exception
when zero_dividethen
dbms_output.put_line('zero_Divide');
when one_dividethen
dbms_output.put_line('one_divide');
when others then
dbms_output.put_line(sqlerrm);
End; /
Swapnali Pawar

29. Swapnali Pawar

30. 2. Using Raise_Application_ErrorStatement-
Declare
vagenumber(10);
age number:=&vage;
Begin
if age < 18 then
Raise_Application_Error(-20008,'Age must be greater than 18 !');
end if;
dbms_output.put_line(‘Voting Allowed after completing 18 years');
Exception
when others then
dbms_output.put_line(sqlerrm);
End;
/
Swapnali Pawar

31. Swapnali Pawar

32. Example.
CREATE TABLE CITIZEN (
ID INT NOT NULL,
NAME VARCHAR (15) NOT NULL,
AGE INT NOT NULL,
PRIMARY KEY (ID)
);
We have created the CITIZEN table with the help of the SQL statement given below.
SELECT * FROM CITIZEN
Swapnali Pawar
Insert values to this table with SQL statements given below:
INSERT INTO CITIZEN VALUES (1, ‘Swapnali', 26);
INSERT INTO CITIZEN VALUES (8, ‘Rugveda', 15);
INSERT INTO CITIZEN VALUES (5, ‘Shree', 37);

33. Swapnali Pawar
Coding Implementation With Exception Handling:
DECLARE
citizen_idcitizen.id%type;
citizen_namecitizen.name%type;
citizen_agecitizen.age%type:= 9;
BEGIN
SELECT id, name INTO citizen_id, citizen_name
FROM citizen
WHERE age = citizen_age;
DBMS_OUTPUT.PUT_LINE ('Citizen id is: '|| citizen_id);
DBMS_OUTPUT.PUT_LINE ('Citizen name is: '|| citizen_name);
EXCEPTION
WHEN no_data_foundTHEN
dbms_output.put_line('No citizen detail found');
WHEN others THEN
dbms_output.put_line('Errors');
END;
/

34. Swapnali Pawar

35. Swapnali Pawar
Student Activity
1.Using Raise statement create user defined exception which raise
an error if sum of 2 numbers is greater than 5000.
2.Using Raise statement create user defined exception which raise
an exception if age <18 and displays ‘Your are not Allowed for
Voting This Year ! ’ Message.
3.Using Raise_Application_Errorstatement create your own
exception code for “Salary must be above 50000 for Loan
Approval !” message.
4.Create table with your name .Add columns Roll_noName Marks
.Enter some records in table. Execute various queries on that table
& if records are not available raise no_data_foundexception.

36. Swapnali Pawar