Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Oracle SQL Developer and Viewing User TYPE Data

1 Min Read

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 🙂

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.

Related Posts

7 Comments

  1. rino Reply

    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. 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. PPS Reply

    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 Post Author

      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. 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!)

Write A Comment