How to Create Views for All Tables with Oracle SQL Developer
- by thatjeffsmith
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 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
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
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
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
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.
-- 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
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!
