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.

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.

 o_in := o (1, 'abc');
o_in := o (1, ‘abc’);

Now we’re debugging.

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

We can see our variables/objects, but we need to 'Step' over to where the action is.
We can see our variables/objects, but we need to ‘Step’ over to where the action is.

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.

Just use your mouse and hover over the object, then drill down into where you want to go...
Just use your mouse and hover over the object, then drill down into where you want to go…

Or, you can interact with the Data grid below.

Pick your poison.
Pick your poison.

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.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

4 Comments

  1. Hernan Baioni Reply

    Jeff, I need some help here. I’m compiling everything for debug, and the objects still show “OPAQUE”. Do I need an aditional privilege or something?

  2. Does this mean we will soon be able to write unit tests using collections. Currently support is very lacking, especially for inheritance.

    • Sorry, there’s no correlation. And many more people use the debugger than the unit testing. I’d like to see enhancements in this area, but don’t have the demand to justify the costs. Plus, Cloud.

Reply To Hernan Baioni Cancel Reply