Exception Handling in PL/SQL

What is Exception Handling?

Exception handling is one of the most important things to learn in PL/SQL. There are so many articles written on it on other blogs and websites but still it is always good to refresh what you are already aware of (maybe you find something more!). In this article, I had tried my best to cover up all necessary information that a novice or experienced PL/SQL developer may need on exception handling.

Exceptions are everywhere around us be it in our colleagues or friends or neighbors, anywhere. But still we find out a way to handle them. Let’s take an example of a nerdy friend in a group of cool people. He is an exception, others in the group will handle him in a different way keeping their friendship intact.

In the same way, PL/SQL codes may face exceptions which must be necessarily handled. It ensures smooth working of programs and helps the programmer to identify the exact cause of why the exceptions are raised. Codes in which exceptions are absent are analogous to an aimless arrow which may fire back at you anytime.

What is the syntax of PL/SQL Exceptions? Show the example

Yes, why not. Below is the syntax of standard PL/SQL exception

Syntax

Above is the basic example of PL/SQL exceptions. We will see little bit more complex ones as and when we move ahead.

What are the types of exceptions?

There are two types of exceptions in PL/SQL

1) System-defined exceptions
2) User-defined exceptions

System Defined Exceptions

Exceptions are nothing but errors or warnings when some violate Oracle standard rules. They are sometimes termed as Pre-defined exceptions also.

List of table of System defined exceptions

Example

User-Defined Exceptions

The exceptions defined by a user in his own language that suits his requirements are termed as a user-defined exception. They have to be explicitly raised and declared in the declaration section of code.

Example

What should I do when I encounter an exception?

Most of the developers use error messages to get popped up in their application for exception notifications. Some of them leave it as it is without returning any message and allows the program to go ahead further. Not-So-Good programmers write exception-less code. Oh, of course, I know you are not one of those Not-So-Goods.

A clever developer defines a ground rule for himself to include exception command in every SQL block of code he writes even though further processing of code is inevitable or not.

What is the advantage of using PL/SQL Exceptions?

Exceptions are essential and integral part of every coding you do in PL/SQL. There are certain reasons why they are very much required in your code block.

With your vigorous testing attitude your code may be running smooth now but what about the future? You pack your bags and leave for the better opportunity but what about someone who takes your ride and has to literally struggle to scavenge an error in your code?

Let’s say after a year or so you are working on same code to rectify the cause of the error that stopped the code block from running smooth? Ah! Now you understand my point, isn’t it?

Advantages

  • Readability of program is improved with exceptions.
  • Debugging of error / bug becomes fast.
  • Efforts and time can be saved.

SQLCODE and SQLERRM

SQLCODE and SQLERRM are two standard functions that return error number and error message in the PL/SQL code. Except for NO_DATA_FOUND (error number = +100) exception, all the error numbers are negative.

Raise_Application_Error

Raise_Application_Error returns a user-defined error message as well as user-defined error code whenever an exception is encountered.

Read here more about Raise_Application_Error

Few Tips for Exception Handling in PL/SQL

Use Locator variables

Sometimes when we write chunks of code, we write only one exception at the end. So whenever an exception arises it becomes difficult for us to identify the SQL which caused it.

For example,

You can use locator variables here if you do not wish to write multiple exception statements.

Use of NULL in Exception

There will be times when you handle exceptions by displaying a warning or error message but there will also be some circumstances where you want your program to be processed further even though an exception is raised. Here, NULL keyword can be very useful to you.

Example

In this PL/SQL block, data in table emp_mast_1 should be filled even if the gender of an employee is returned or not.

Use Appropriate Names and Messages

The naming convention is one of the crucial things when it comes to writing code irrespective of whatever coding language you use. Improper names and messages could mislead a developer or user. Error messages with half information will not significantly reduce the time taken to find the root cause.

For Example,

In the above example, if an exception is raised we will not be able to know for which employee it is caused.

So the use of appropriate error messages is fairly a good practice and an important too. We can write an exception message as below to identify an employee for whom it is raised and due to which reason.

If you liked the above post, please leave your comments below.

You can also Subscribe here to stay updated on latest posts of OracleMine.com.

I am the owner of OracleMine.com. I believe you should never quit in life if you want to achieve something. Just work hard, leave it all on supreme power to deliver the results. I like blogging and writing very much. In my past time, I like to read books too, especially fictional novels.

Leave a Reply

Your email address will not be published. Required fields are marked *