How to efficiently debug PL/SQL code

Almost every day I remember proverb/adage: if you haven’t discovered anything new for a while, it doesn’t mean you know everything and there is nothing left that you can learn.

It only means you stop learning.

I started with SQL Developer when it was internal and later alpha project inside the Oracle with a code name “project Raptor”.

Although I’ve been constantly using that product (and Data Modeler later) for 13 years now, there are still some functionality that I’ve discovered recently.

In this post I’ll explain how to debug exception in you code quickly.

I assume you know what are prerequisites for debugging.

I have the following anonymous block of PL/SQL code:

declare 

l_empno number := 7654;
l_return_value varchar2(1);

begin 
dbms_output.put_line('Starting anonymous block');

l_return_value := test_debugging(l_empno);

end;

I also need to create one function where I deliberately generate error condition:

create or replace function test_debugging(p_empno in number) return varchar2
as
l_cnt number;

begin 
dbms_output.put_line('EMPNO = ' || p_empno);

--select count(*) into l_cnt from emp where empno = p_empno;

--error condition as empno is of type Number
select count(*) into l_cnt from emp where empno = 'ABC';

if l_cnt = 1 then 
    return 'Y';
else 
    return 'N';
end if;

exception 
   when others then
     dbms_output.put_line('Error condition');
     dbms_output.put_line(dbms_utility.format_error_backtrace);
     raise;
end;

Now it’s important to compile function for debug mode.

alter function test_debugging compile debug;

To start debugging anonymous block of code, all you need to do is to enter Ctrl+Shift+F10

After that you need to click on Breakpoints tab, and then right mouse click on Oracle exception, Persistent, like in the following screenshot.

A new window will appear.

Now all you need to do is to enable: Break for Caught Exceptions check box.

Finally you need to click on Resume button (F9).

When your code will hit the error, program execution will stop and cursor will be exactly on a line that is throwing an error.

Now you can inspect all variables (data, watches, smart data …) as usual which allow you to quickly find what is a reason for error.

Final output from debugger will look like this:

Exception breakpoint: TEST_DEBUGGING.pls:12, $Oracle.EXCEPTION_ORA_1722, 
Exception breakpoint: TEST_DEBUGGING.pls:24, $Oracle.EXCEPTION_ORA_1722, 
ORA-01722: invalid number
ORA-06512: at "SCOTT.TEST_DEBUGGING", line 24
ORA-06512: at line 9
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()
Starting anonymous block
EMPNO = 7654
Error condition
ORA-06512: at "SCOTT.TEST_DEBUGGING", line 12

This is because I’m using another functionality, very often overlooked, which help you to get enough data to quickly discover where is the error and what is causing it.

It’s format_error_backtrace procedure which is part of the dbms_utility package:

dbms_output.put_line(dbms_utility.format_error_backtrace);

Summary:

Imagine that your code is complex and involves many packages/procedures/functions etc.

Instead of setting many breakpoints, or to step into the code line by line, within a second you’ll be in position to discover what is really causing the error.

I have to mention that many Oracle developers and DBA’s are still using

dbms_output.put_line

technique, or even worse: inserting content of variables in some debug table inside the database and query those debug table to figure out what is going on (I see such things in one of the global Fortune 100 company).

Comparing that with technique that I’m suggesting in this post, I hope you realize that my technique is light years ahead.

---