Got this question over the weekend via a friend and Oracle ACE Director, so I thought I would share the answer here.

If you want to quickly generate DDL to create VIEWs for all the tables in your system, the easiest way to do that with SQL Developer is to create a data model.

Wait, why would I want to do this? StackOverflow has a few things to say on this subject…

So, start with importing a data dictionary.

Step One: Open of Create a Model

In SQL Developer, go to View – Data Modeler – Browser.

Then in the browser panel, expand your design and create a new Relational Model.

Step Two: Import your Data Dictionary

This is a fancy way of saying, 'suck objects out of the database into my model'
This is a fancy way of saying, ‘suck objects out of the database into my model’

This will open a wizard to connect, select your schema(s), objects, etc.

Once they’re in your model, you’re ready to cook with gas 🙂

I’m using HR (Human Resources) for this example.

You should end up with something that looks like this.

Our favorite HR model
Our favorite HR model

Now we’re ready to generate the views!

Step Three: Auto-generate the Views

Go to Tools – Data Modeler – Table to View Wizard.

I don't want all my tables included, and I want to change the naming standard
I don’t want all my tables included, and I want to change the naming standard

Decide if you want to change the default generated view names
By default the views will be created as ‘V_TABLE_NAME.’ If you don’t like the ‘V_’ you can enter your own. You also can reference the object and model name with variables as shown in the screenshot above. I’m going to go with something a little more personal.

The views are the little green boxes in the diagram
The views are the little green boxes in the diagram

Can’t find your views? They should be grouped together in your diagram. Don’t forget to use the Navigator to easily find and navigate to those model diagram objects!

Step Four: Generate the DDL

Ok, let’s use the Generate DDL button on the toolbar.

Un-check everything but your views
Un-check everything but your views

If you used a prefix, take advantage of that to create a filter. You might have existing views in your model that you don’t want to include, right?

Once you click ‘OK’ the DDL will be generated.

[sql collapse=”true”]
— Generated by Oracle SQL Developer Data Modeler 4.0.0.825
— at: 2013-11-04 10:26:39 EST
— site: Oracle Database 11g
— type: Oracle Database 11g

CREATE OR REPLACE VIEW HR.TJS_BLOG_COUNTRIES ( COUNTRY_ID
, COUNTRY_NAME
, REGION_ID )
AS SELECT
COUNTRY_ID
, COUNTRY_NAME
, REGION_ID
FROM
HR.COUNTRIES ;

CREATE OR REPLACE VIEW HR.TJS_BLOG_EMPLOYEES ( EMPLOYEE_ID
, FIRST_NAME
, LAST_NAME
, EMAIL
, PHONE_NUMBER
, HIRE_DATE
, JOB_ID
, SALARY
, COMMISSION_PCT
, MANAGER_ID
, DEPARTMENT_ID )
AS SELECT
EMPLOYEE_ID
, FIRST_NAME
, LAST_NAME
, EMAIL
, PHONE_NUMBER
, HIRE_DATE
, JOB_ID
, SALARY
, COMMISSION_PCT
, MANAGER_ID
, DEPARTMENT_ID
FROM
HR.EMPLOYEES ;

CREATE OR REPLACE VIEW HR.TJS_BLOG_JOBS ( JOB_ID
, JOB_TITLE
, MIN_SALARY
, MAX_SALARY )
AS SELECT
JOB_ID
, JOB_TITLE
, MIN_SALARY
, MAX_SALARY
FROM
HR.JOBS ;

CREATE OR REPLACE VIEW HR.TJS_BLOG_JOB_HISTORY ( EMPLOYEE_ID
, START_DATE
, END_DATE
, JOB_ID
, DEPARTMENT_ID )
AS SELECT
EMPLOYEE_ID
, START_DATE
, END_DATE
, JOB_ID
, DEPARTMENT_ID
FROM
HR.JOB_HISTORY ;

CREATE OR REPLACE VIEW HR.TJS_BLOG_LOCATIONS ( LOCATION_ID
, STREET_ADDRESS
, POSTAL_CODE
, CITY
, STATE_PROVINCE
, COUNTRY_ID )
AS SELECT
LOCATION_ID
, STREET_ADDRESS
, POSTAL_CODE
, CITY
, STATE_PROVINCE
, COUNTRY_ID
FROM
HR.LOCATIONS ;

CREATE OR REPLACE VIEW HR.TJS_BLOG_REGIONS ( REGION_ID
, REGION_NAME )
AS SELECT
REGION_ID
, REGION_NAME
FROM
HR.REGIONS ;

— Oracle SQL Developer Data Modeler Summary Report:

— CREATE TABLE 0
— CREATE INDEX 0
— ALTER TABLE 0
— CREATE VIEW 6
— CREATE PACKAGE 0
— CREATE PACKAGE BODY 0
— CREATE PROCEDURE 0
— CREATE FUNCTION 0
— CREATE TRIGGER 0
— ALTER TRIGGER 0
— CREATE COLLECTION TYPE 0
— CREATE STRUCTURED TYPE 0
— CREATE STRUCTURED TYPE BODY 0
— CREATE CLUSTER 0
— CREATE CONTEXT 0
— CREATE DATABASE 0
— CREATE DIMENSION 0
— CREATE DIRECTORY 0
— CREATE DISK GROUP 0
— CREATE ROLE 0
— CREATE ROLLBACK SEGMENT 0
— CREATE SEQUENCE 0
— CREATE MATERIALIZED VIEW 0
— CREATE SYNONYM 0
— CREATE TABLESPACE 0
— CREATE USER 0

— DROP TABLESPACE 0
— DROP DATABASE 0

— REDACTION POLICY 0

— ERRORS 0
— WARNINGS 0
[/sql]

You can then choose to save this to a file or not.

This has a few steps, but as the number of tables in your system increases, so does the amount of time this feature can save you!

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.

17 Comments

  1. Hi Jeff,

    I need to generate several custom views for each table. I know it can be done via DDL transformation. Could you point me to a reference script so that I can get an idea to create my own?

    Thanks for your help in advance!

    – Senthil

  2. How to export that Generated views to excel as a separate sheet within the excel.

  3. Oracle SQL Data modeler 4.0 does not create Database Model for Mysql database,does it ?

    • well,thanks a lot i haven’t heard of it before i look forward to use it.

  4. How to define a naming standard in Oracle SQL Data modeler 4.0 . I know in previous version it is available in Tools–> Preferences–> Data Modeler–> Naming Format , but in version 4.0 , I couln’t find it

    • We moved those from the application preferences to the design preferences – so you can have different naming standards per design.

      Right click on the Design in the browser, and open the ‘Design Properties’ and go to the Settings, Naming Standard page.

  5. Marcus Howell Reply

    Hmmmm…

    It seem that a blank field is no good. When I specified the directory it seemed to work OK.
    I followed your instructions I searched for the defaultRDBMSsites.xml and it was in the C:\Program Files\datamodeler\datamodeler\types directory:

    So in the ‘Default system Types directory’, I have set to:
    C:\Program Files\datamodeler\datamodeler\types

    This works OK. The Blank entry was causing the problem.

    Thanks for this

    many thanks

  6. Marcus Howell Reply

    Hi, I am getting the same thing. I get all the way through but fails with the same error message the log is:

    Oracle SQL Developer Data Modeler 4.0.0.825
    Oracle SQL Developer Data Modeler Import Log
    Date and Time: 2013-12-13 09:04:23 GMT
    Design Name: Untitled_1
    RDBMS : null

    All Statements: 1
    Imported Statements: 0
    Failed Statements: 0
    Not Recognized Statements: 1

    Any Ideas? With the demise of reverse engineering in Visio I was hoping that data modeler might be able to help me out. Unfortunately failed at the first post.

  7. Jeff,

    Just did this File->DataModeller->Import->DataDictionary then navigated to a Oracle 10g database connection and chosen “emp” and “dept” tables present in Scott schema. When i clicked Finish got this error message.

    oracle.dbtools.crest.model.design.storage.StorageDesign$NullStorageDesign
    cannot be cast to
    oracle.dbtools.crest.model.design.storage.oracle.StorageDesignOracle

    I am using SQL Developer Version 4.0.0.13

    • Check setting for “system data types directory” in “Preferences>Data modeler”. It should be clear or point to directory which contains defaultRDBMSsites.xml and types.xml that comes with DM 4.0 EA1.

    • Jeff,

      I did the below steps in SQL Developer Version 4.0.0.13, I am not sure why are you refering to “DM 4.0 EA1” ?

      BTW i checked with sql-developer EA3 in “Preferences>Data modeler” I dont see any “system data types directory”

      Just did this File->DataModeller->Import->DataDictionary then navigated to a Oracle 10g database connection and chosen “emp” and “dept” tables present in Scott schema. When i clicked Finish got this error message.

  8. In SQL Developer, go to View – Data Modeler – Browser.
    Then in the browser panel, expand your design and create a new Relational Model.

    Just click the Table Icon to your worksheet and navigate to Table_Properties -> columns ,

    1) When datatype is chosen as Logical TYPE dropdown is loaded with only unKnow values

    2) when switched to Structural dropdown is loaded with “SDO_GEOMETRY” and “XMLTYPE”

    Is this a bug please confirm, I am using SQL Developer Version 4.0.0.13

    • My structural list displays everything as expected. I’m in 4.0.0.13 as well.

      Can you try extracting the zip to a new directory and try it again? Wondering if something got corrupted on your first ‘install.’

Write A Comment