In this blog I will give you a tip to how to find error line number in PL/SQL program easily. Suppose you have a lengthy PL/SQL procedure or function which might be a standalone or inside a package and while executing, it is throwing an exception / error.

And because exception is also being handled properly in that PL/SQL program, so what happening is when it is giving an error, it is being handled by exception and showing the line number of that exception part of PL/SQL program, which is written at the end of the program. Below is the sample PL/SQL procedure illustrating this.

Create or Replace Procedure xyz
is
Begin
— many code lines
—
—
—
Exception
When Others then
— your custom exception handler
End;

In that case it is hard to find error line number in PL/SQL program, which is actually creating the problem. To find this you need to just comment that exception part temporarily and compile that procedure / function or package and execute again to get the actual error with exact line number of PL/SQL program.

Below is the example:

Create or Replace Procedure xyz
is
Begin
— many code lines
—
—
—
/* Exception
When Others then
— your custom exception handler */
End;

After getting the error information and line number, don’t forget to un-comment that exception part and compile again, so that it should be in its original state.

This will definitely help to find error line number in PL/SQL.