ThatJeffSmith

Viewing Complex Types in Oracle SQL Developer Data Modeler

Instead of having a column of type VARCHAR2 for a ‘Country,’ you might want to create an COUNTRIES type. Said COUNTRY would have an ID, NAME, and LOCATION.

To make things even more fun, LOCATION would also be a custom type of LOCATIONS, having even more attributes/columns.

To model these from scratch, you would build them in your design as part of the Data Types Model. You could then take advantage of them in your Relational Model Diagram when defining the column data type.

If you reverse engineer an existing system, taking care to INCLUDE the TYPEs, then you can see exactly how the data is stored in your diagrams very nicely in Oracle SQL Developer Data Modeler.

So for a given example:


CREATE OR REPLACE TYPE location_typ 
    AS OBJECT 
    ( 
        location_id NUMBER (4) , 
        street_address VARCHAR2 (40) , 
        postal_code VARCHAR2 (12) , 
        city VARCHAR2 (30) , 
        state_province VARCHAR2 (25) 
    ) NOT FINAL 
; 
/ 

CREATE OR REPLACE TYPE nt_location_typ 
    IS TABLE OF location_typ 
; 
/ 

CREATE OR REPLACE TYPE country_typ 
    AS OBJECT 
    ( 
        country_id CHAR (2) , 
        country_name VARCHAR2 (40) , 
        locations nt_location_typ 
    ) NOT FINAL 
; 
/ 

CREATE OR REPLACE TYPE nt_country_typ 
    IS TABLE OF country_typ 
; 
/ 

CREATE TABLE region_tab 
    ( 
     region_id   NUMBER , 
     region_name VARCHAR2 (25) , 
     countries   nt_country_typ 
    ) 
    NESTED TABLE countries STORE AS nt_countries_tab 
    ( NESTED TABLE locations STORE AS nt_locations_tab ) 
; 

Basically, create some types, and then some nested table of said types, and then use those as the data type definitions in a table, and then import that into a Relational model in SQL Developer.

Here is what you get:

The Relational Diagram, Complex Types Expanded

You can tell the Modeler to expand one level of complex types. In this case, instead of JUST seeing that the COUNTRIES columns is of type ‘NT_COUNTRY_TYP’, you can see that type as defined as a ID, NAME, and LOCATION.

Note that this is NOT the default behavior, you'll need to switch this ON if you want to see the types expanded.

Note that this is NOT the default behavior, you’ll need to switch this ON if you want to see the types expanded.

I mentioned to our developer that it might be nice to allow for the nested types to be expanded further. He agreed and you can see this available in the next update, Cue the legal disclamer about promises on new features and how things are likely to change.

What it will PROBABLY look like in the next release.

What it will PROBABLY look like in the next release.

But for now, you can see the types much more clearly – today.

Since this is a nested type, you may want to see the relationship expanded even further. This is available with

The Data Types Model Diagram

This nicely shows the nested types and their relationship.

This nicely shows the nested types and their relationship.

Power Tip: Use the New Document Tab Group feature to see two diagrams, side-by-side.

If you open the data type properties, you can see exactly how each attribute is defined:

I'm not going to go any deeper than this!

I’m not going to go any deeper than this!

You can also see how things are setup in the…

The Physical Model

The information is READ ONLY here - you'll need to make changes via the Data Types Model.

The information is READ ONLY here – you’ll need to make changes via the Data Types Model.

One more thing…

Want to Learn More about Super and Sub Typing?

Check out this nice SlideShare deck from our developer, Philip.