Offline Database Definitions with Oracle JDeveloper 10g-Reconciling with Online Schema

JDeveloper 10g allows developers to work with database objects offline (without interacting directly with a database). Developers can import and then edit schema from an existing database or create new database definitions offline from scratch. Some of the activities one can perform with offline database definitions include generating diagrams and creating SQL DDL files to run against databases.

JDeveloper even provides a Reconcile feature that will create a DDL script with the needed ALTER commands to change an online database schema to match the changes ade to the offline schema, which is very useful for making changes to a test database in a controlled way. Changes can be made by generating an offline schema from the online database, then changing the offline schema and generating a DDL script to run against the test database. After testing this same SQL script would then be run against other test databases and ultimately against production.

This tutorial creates an offline schema version of the PO Example tables from the ML book, adds several fields to the EMPLOYEE table, creates an new offline table EMPLOYEE_SAL, then generates DDL scripts to change the database to match the offline changes..

Select New to open the New Gallery.

Select Offline Database Objects from the Database Tier list. Then Select Offline Database Objects Imported from Database. Then click OK to begin the Import Offline Database Objects wizard.

Name the offline schema:

Select database to Connect to to capture schema:

Select Objects to load into offline schema (below ACCOUNT, DEPARTMENT and EMPLOYEE tables are selected):

New Offline schema is shown below in the Navigator:

Double-click on the EMPLOYEE table to open the Edit Offline Table dialog below. Here one can add or change fields, etc. Below the ADDRESS field is added:

After adding CITY, STATE and ZIP fields and closing the Edit dialog these new fields now appear in the Structure view below:

Right-click on OFFLINE_PO_SCHEMA and select New Offline Table. This starts the wizard below:

Below the new table EMPLOYEE_SAL is added with 3 fields:

To create the DDL files, Right-click again on OFFLINE_PO_SCHEMA and select Generate or Reconcile Objects:

For changes, select UPDATE to generate an ALTER script for the added fields:

The SQL script appears in JDevleoper-it is also listed in the Resources folder:

For the new EMPLOYEE_SAL table, select CREATE to generate the Creation SQL script:

Below is the creation script for creating the new EMPLOYEE_SAL table:

Written by: Wayne Holle ()