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?

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.

15 Comments

  1. Arun S Rajagopalan Reply

    How do I view a table type that is in SELECT statement using SQL Developer? The output variable shows it as

    Address Collection:
    oracle.jdbc.proxy.oracle$1jdbc$1proxy$1NullProxy$2java$1sql$1Array$$$Proxy@7fbefb9a
    Hours Collection:
    oracle.jdbc.proxy.oracle$1jdbc$1proxy$1NullProxy$2java$1sql$1Array$$$Proxy@3204094d
    Contact Collection:
    oracle.jdbc.proxy.oracle$1jdbc$1proxy$1NullProxy$2java$1sql$1Array$$$Proxy@18fee7d9

    I am not able to look at the rows in those collections.

  2. Two questions:
    There is an inconsistency between the watches and smart data tabs (it may be by design, that is fine). There is no left column for the green dots. Why I bring it up has to do with the next question.

    If I add a watch for an entire table of table of record type, that works fine . However, if I expand the tree to my_package#.t_thing._values[1]._value._values[1]._value.record_item and right-click add to watch, it does not get shown in the watch, instead, the watch is populated with: _values[0]._value._values[0]._value.record_item and though there is a value displayed in the original watch, when I expand all of the way to the record item, there is nothing in the new watch that I added with a right click. Is it something that I’m doing incorrectly?

    Having the ability to see what is going on in package 1 while I’m tracing through package 2, would be a super feature.

    • Having the ability to see what is going on in package 1 while I’m tracing through package 2, would be a super feature.

      The scope changes when you go from executing pack2 from pack1 – I don’t think you can watch one unit’s variables while executing another’s…but I’m on vacation until next week. I’ll double-check when I get back.

    • Hey Jeff,

      A gentle reminder about the above question? How can one add a watch at a lowe eg my_package#.t_thing._values[1]._value._values[1]._value.record_item rather than only on the top most level my_package#.t_thing_values?

      Furthermore, I want to be able to specify that I want to look at the global variables of a specific package (maybe two packages have the same global variable name).

    • if you tried your watch at that level and it didn’t work, it’s probably not possible

      i don’t understand what you’re asking about the global vars

  3. Jeff,

    Is there any way to view collections in SQL Developer 4’s PLSQL debugger? I keep getting opaque values when I try to look at the real time values of data created from types like this:

    CREATE TYPE my_type IS OBJECT (
    colA VARCHAR2(100),
    colB VARCHAR2(100)
    );
    CREATE TYPE my_type_list IS TABLE OF my_type;

    • Dave,

      if you have an idea how to solve this: please post!
      I’m using the newest version of the SQL Developer.. and it’s driving me nuts not to see the values of data types like above.
      A bug?

    • I need some code guys, what exactly are you trying to debug?

      Here’s something I borrowed/hacked up from Steven from Oracle Magazine…declares a type and uses bulk collection into a collection of said %type.

      [sql]
      create or replace PROCEDURE process_all_rows
      IS
      TYPE employees_aat
      IS TABLE OF employees%ROWTYPE
      INDEX BY PLS_INTEGER;
      l_employees employees_aat;
      BEGIN
      SELECT *
      BULK COLLECT INTO l_employees
      FROM employees;

      FOR indx IN 1 .. l_employees.COUNT
      LOOP
      — analyze_compensation (l_employees(indx));
      dbms_output.put_line(l_employees(indx).hire_date);
      END LOOP;
      END process_all_rows;
      [/sql]

      I’m able to see these values…

      SQL Developer debug watch types

      If this isn’t what you’re looking for, give me a quick stub of code using scott.emp as an example…

    • Perhaps the package which contains the type which you need to debug is not compiled with debug?

    • sajjad ahmed

      Hey i found the solution for the opaque values..

      Do – compile for debug for both package/function/procedure AND for TYPE from SQLDEVELOPER.

      In this case below my_type

      CREATE TYPE my_type IS OBJECT (
      colA VARCHAR2(100),
      colB VARCHAR2(100)
      );

    • Hernan Baioni

      I’m compiling all the objects and the variable is still OPAQUE.
      Can you give further details on what you did? Do you think I need a privilege (other than debug) enabled?

  4. It looks like my WP plugin SyntaxHighlighter isn’t working for some reason. Oh joy. Thanks for bringing it to my attention Salvador!

Write A Comment