A ‘quick’ question came in this morning: “Jeff, does SQL Developer show contents of Object type columns? I’m advised not…

I wanted to say ‘Yes’ and then just run away laughing maniacally, but I thought maybe I should put together a quick example instead. So here goes. And by the way, welcome to 2013 everyone 🙂

Creating a USER TYPE

From the DOCS

CREATE TYPE demo_typ1 AS OBJECT (a1 NUMBER, a2 NUMBER);

Create and Populate a Table using the new Type

CREATE TABLE demo_tab1 (b1 NUMBER, b2 demo_typ1);
 
INSERT INTO demo_tab1 VALUES (1, demo_typ1(2,3));

Querying and Viewing the TYPE data using SQL Developer

Now I’m not a fan of ‘SELECT *’ from queries, but in this case I’ll give you a pass. Or, just open the table editor in the connection tree and go to the Data tab. I’ll use the SELECT * from method.

Click on the ‘Pencil’ icon to drill into the object type data.

So a quick and dirty post, but now I have one less scenario to code up when needing to answer FAQ SQL Developer questions 🙂

thatjeffsmith
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.

7 Comments

  1. Avatar

    is it possible to get the same output as in SQL Plus to this select * from demo_tab1?
    I mean to see data without using “Click on the ‘Pencil’ icon to drill into the object type data.” but actual data

  2. Avatar
    Hossein Ziayan Reply

    Thanks, It solved my problem. I just recompiled the types and now I can see the objects values in debug tab.

  3. Avatar

    Hi Jeff,
    Many times when we debug object types which are inherited from a base Oracle object type sqldeveloper will show it as OPAQUE. Can you please guide on this regard.

    Regards,
    PPS

    • thatjeffsmith

      Sounds like that OBJECT TYPE hasn’t been compiled with DEBUG. And make sure all of the dependent objects have been. At least, that’s what I would check first.

  4. Avatar
    Allan Lewis Reply

    …and when will we be able to see the contents of types and tables of types when debugging PL/SQL? (Or if this is already possible but I’m doing something wrong, please let me know!)

Reply To Allan Lewis Cancel Reply