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:
Now Expand the TAB Tree Node
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.
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.
Having trouble getting the watches or data panels to show anything? You’ve remembered to compile your procedure ‘For Debug,’ right?