Our stored procedures often capture information and store that data as a string or number. But sometimes we need to process more than just a single value. PL/SQL allows you to do this via collections. Now, if you’re looking for help getting started with collections, I would start with

Once you have a program working, and you want to know what your data looks like before it processes or passes the data to another program, I recommend using our PL/SQL debugger. The debugger allows you to run your program, step-by-step, and inspect what the code is doing, line-by-line. If you need a quick, ‘Debugger 101‘, then you’re in luck :)

Once you’re debugging, you’re ready to ‘watch.’ Need help figuring our how watches work, and when to use a watch versus the Data or Smart Data panel?

Stepping Through the ‘Table’

Your PL/SQL table has one or more rows, and now you want to know what’s in those rows, right?

Your code may look a little something like this:

   OPEN c1; --c1 is a cursor tied to a query that is executed
   LOOP
      FETCH c1 INTO tab (len + 1); -- tab is a 'table of table%rowtype'
      EXIT WHEN c1%NOTFOUND;
      len := len + 1;
   END LOOP;

Once you invoke the debugger in SQL Developer, you’ll notice the Smart Data Panel has an entry that matches our ‘tab’ object:

There’s more than meets the eye!

Now Expand the TAB Tree Node

Walk the tree to get to the data you want to inspect

My indexed table happens to be indexed by an integer. This integer grows as my cursor loop runs. I can drill into one of the rows and see the data being stored.

As I step through the loop, I’ll see more and more rows appear.

Quick Inspect

Now maybe I don’t want to click and expand to see what’s happening. You can instead do a ‘Quick Inspect’ or a ‘peek’ to see what’s being stored. That’s what the ‘little green dots’ indicate.

Click on the green ‘bullet’ to see the data

Mmmm, tasty data

Having trouble getting the watches or data panels to show anything? You’ve remembered to compile your procedure ‘For Debug,’ right?