We cannot say that the code is robust until it can handle all the exceptions. Bugs and abrupt termination of a program are the nightmares of a programmer’s life. No programmer wants to develop a code which will crash in a midway or behave unexpectedly. Thus for the smooth execution of a software it is necessary to handle all kinds of exceptions.
Knowing your problem is the first step towards finding its solution. So let’s learn more about exception handling in Oracle database.
What is an Exception?
Any abnormal condition or say event that interrupts the normal flow of your program’s instructions at run time is an exception. Or in simple words you can say an exception is a run time error.
Info Byte:
Exceptions are designed for run time error handling rather than compile time error handling. Errors that occur during compilation phase are detected by the PL/SQL compiler and reported back to the user.
Types of exceptions
There are two types of PL/SQL exceptions in Oracle database.
- System-defined exceptions and
- User-defined exceptions
System-Defined Exceptions
System-defined exceptions are defined and maintained implicitly by the Oracle server. These exceptions are mainly defined in the Oracle 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.
Info byte:
Oracle avails two utility functions SQLCODE & SQLERRM to retrieve the error code and message for last occur exception.
User-Define Exceptions
Unlike System-Define Exception, User-Define Exceptions are raised explicitly in the body of the PL/SQL block (more specifically inside the BEGIN-END section) using the RAISE Statement.
How to Declare a User-Define Exception in Oracle Database.
There are three ways of declaring user-define exceptions in Oracle Database.
- By declaring a variable of EXCEPTION type in declaration section.
You can declare a user defined exception by declaring a variable of EXCEPTION datatype in your code and raise it explicitly in your program using RAISE statement and handle them in the Exception Section.
- Declare user-defined exception using PRAGMA EXCEPTION_INIT function.
Using PRAGMA EXCEPTION_INIT function you can map a non-predefined error number with the variable of EXCEPTION datatype. Means using the same function you can associate a variable of EXCEPTION datatype with a standard error.
- RAISE_APPLICATION_ERROR method.
Using this method you can declare a user defined exception with your own customized error number and message.
That’s it for this section. Hope you enjoyed reading this brief introduction to exception handling in Oracle Database. In this blog I tried to answer a few questions which you can expect in your Oracle Database Certification as well as in the Interview.
Stay tuned as in the next tutorial we will do some cool examples explaining the above mentioned concepts which will help you in enhancing your knowledge and give you a strong hold on the concepts of PL/SQL exception handling. Thanks & have a great day!
