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 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.
Now we’re ready to generate the views!
Step Three: Auto-generate the Views
Go to Tools – Data Modeler – Table to View Wizard.
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.
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.
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!
17 Comments
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
Lots of great sample/examples on AskTom and StackOverflow on these topics.
How to export that Generated views to excel as a separate sheet within the excel.
Tools, Database Export. Select Excel, choose single file. Select your views. Go 😀
Oracle SQL Data modeler 4.0 does not create Database Model for Mysql database,does it ?
No, the MySQL team has their own IDE/modeling tool – the MySQL Workbench.
well,thanks a lot i haven’t heard of it before i look forward to use it.
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.
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
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.
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.
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.’
I am still seeing the same error Jeff.