Debugging Associative Arrays

My favorite development tool is PL/SQL Developer by Allround Automations. The Debugger included with this tool is great, I really like it… but… not for Associative Arrays.
Let’s take a look at some code we want to debug

type emp_rt is record
(ename    emp.ename%type
,hiredate emp.hiredate%type
,sal      emp.sal%type
);
type emp_tt is table of emp_rt
index by pls_integer;

First we declare a Record, based on some of the columns of the infamous EMP table. Next we declare the Associative Array based on the Record structure.
Then we need a (packaged) procedure, which we are going to debug. The type declaration and the procedure are both in a Package, of course.

procedure test
is
emps emp_tt;
idx  pls_integer;
begin
select ename
    , hiredate
    , sal
 bulk collect
 into emps
 from emp
;
idx := emps.first;
while idx is not null
loop
  dbms_output.put_line ('Employee: '||emps(idx).ename);
  idx := emps.next(idx);
end loop;
end test;

PL/SQL Developer has a Test Window, where you can run an anonymous block. It is possible to call this window whenever you feel like, no need to be in the context of a Package like in SQL Developer. Nor does the package need a breakpoint. And this is a good thing, 'cause most of time I forget to set breakpoints.

When you are stepping through the code and you want to see the content of the Associative Array, you will get the message:

And that's too bad.

SQL Developer to the Rescue

SQL Developer, Oracle's free IDE, also has a Debugger. And what is really nice about this debugger, is that you can view the content of the Associative Array.

1. Set a Breakpoint in the procedure (this can only be done when you are in edit mode)
   
2. Compile the Package in Debug (with the black compile button
3. Choose Run --> Debug from the menu

4. Run the procedure from the Debug window
5. Execution halts at the Breakpoint

You can look on the "Smart Data" tab to see the content of the Associative Array.

Simply Brilliant! Great Job, Sue!