Debugging PL/SQL Collections Revisited

I talked about using the debugger to inspect a collection in PL/SQL using SQL Developer a few years ago.

It’s been so long in fact that when Steven asked if I had a post on it, I said ‘no.’ We had gotten a question from a reader on how to look at collections while you’re debugging them. If you want to read about this topic in general, Steven’s article in Oracle Magazine is a great place to start.

Tweets by sfonplsql

Anyway, here is the first post I wrote on the topic, but I didn’t really go into the details of the code itself…

Here’s that post.

Today, I’m revisiting this topic, and I’m going to share all the code you need to follow along.

Open a SQL Worksheet, and run this as a script.

CREATE OR REPLACE TYPE o IS OBJECT
(
   id INTEGER,
   nm VARCHAR2 (10)
)
/
 
CREATE OR REPLACE TYPE nt IS TABLE OF o
/
 
CREATE OR REPLACE PACKAGE p
IS
   TYPE r IS RECORD
   (
      id   INTEGER,
      nm   VARCHAR2 (10)
   );
 
   TYPE aat IS TABLE OF r
      INDEX BY PLS_INTEGER;
 
   PROCEDURE do_stuff (r_in IN r, aat_io IN OUT aat);
 
   PROCEDURE do_stuff (o_in IN o, nt_o OUT nt);
END;
/
 
CREATE OR REPLACE PACKAGE BODY p
IS
   PROCEDURE do_stuff (r_in IN r, aat_io IN OUT aat)
   IS
   BEGIN
      IF r_in.id IS NOT NULL
      THEN
         aat_io (1) := r_in;
      ELSE
         aat_io (5) := r_in;
      END IF;
   END;
 
   PROCEDURE do_stuff (o_in IN o, nt_o OUT nt)
   IS
      lnt   nt := nt ();
   BEGIN
      lnt.EXTEND;
      lnt (lnt.LAST) := o_in;
      nt_o := lnt;
   END;
END;
/

Find your TYPEs and new Package in the tree. Compile them WITH DEBUG. — I forgot to do this, at first. If YOU forget to compile the TYPE for debug, then when we try to ‘peek’ into the type values in the collection, the debugger will complain that its ‘Opaque.’

Now we’re going to start a debug session on our package.

Click the ‘bug’ button.

Then we need to init the input for our overloaded procedure – we’re going to work with the ‘O’ one.

Now we’re debugging.

We’re on line #1, and nothing’s happened, yet.

And because we compiled our TYPEs with debug, we can see into them with the debugger.

And because our type is a table of this other type, which is a combo of an integer and a 10 character string, that’s what we see. And since it’s a table of this object, we have rows. And we have 1 row. So we’re stepping into row 1 of the table, and the values are there.

Or, you can interact with the Data grid below.

This is much easier with the newer debugger that SQL Developer uses, DBMS_DEBUG_JDWP. It’s able to work with non-scalar objects like collections. PL/SQL IDEs and debuggers that use DBMS_DEBUG require you to augment the source and declare local variables and assign them the values of the collection you want to ‘watch’ and interact with. So bear that in mind if you’re reading this about SQL Developer but using another debugger.