ThatJeffSmith

Oracle SQL Developer and Viewing User TYPE Data

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 :)