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

Viewing Complex Types in Oracle SQL Developer Data Modeler

3 Mins Read

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.

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

4 Comments

  1. Yash Reply

    Hi,
    I have got a type which is further made up of attributes of multiple datatypes including other user defined types.

    I am trying to assign the values but I keep on getting ‘reference to uninitialised collection’ error, even though I initialise all the types.
    Is there any specific rule to initialise type within type?

  2. Amrut kumbar Reply

    Hi,

    I need a help in changing data type in Oracle SQL developer data modeler. I have around 100 tables, and having some string columns with nvarchar. Now I want to make all nvarchar to CHAR. Is there any option in Oracle SQL developer data modeler to change data type globally. So that in a single shot we can change data types.

    Thanks & Regards,
    Amrut

    • thatjeffsmith Post Author

      I think you’ve just described one of the major reasons to employe DOMAINs. This allows you to define a column property using a global value, and if you ever need to change it, you do it in one place.

      You could try the advanced search, find all of the NVARCHAR data type name valued columns, and use the Properties dialog to replace them with CHAR.

      Or you could generate the DDL, use a text editor to search and replace, and then re-import that DDL to a new design.

      Or you could write a custom transformation script, but you’ll need to know javascript.

    • George

      Hi – I have been using Data Modeler in a university course and I find it a very useful tool. One issue I have not been able to find solution for is modeling nested tables. I can create varrays, but not nested tables. The above figures illustrate nested tables – how was this done?

Write A Comment