Introduction to PL/SQL exception handling in Oracle database

By Manish Sharma

PL/SQL Exception Handling

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.

  1. System-defined exceptions and
  2. User-defined exceptions

System-Defined Exceptions

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.

 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.

  1. By declaring a variable of EXCEPTION type in declaration section.

  2. 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.

  3. Declare user-defined exception using PRAGMA EXCEPTION_INIT function.

  4. 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.


  6. 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.

If you find this blog helpful and learnt something new then do make sure to Share this with your friends on your social media. Also you can send your suggestions on my twitter @RebellionRider [official twitter handle]. Have a great day!

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

NO SQL Script used in Video and in this article

Do Not Drink & Drive Use My Uber Code "UberRebellionRider"
SignUp using my referral code and get first ride worth $20 FREE

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn
  •          View Manish Sharma's profile on LinkedIn