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