Modeling Data with Offline Database in JDeveloper

For Oracle Applications developers, the JDeveloper offline database modeler replaces the Oracle Designer repository, or CASE as it was referred to. Applications developers should not use SQL DDL scripts for deployment and source control of database objects, because they tend to error-prone and do not serve as a single source of truth. Instead, developers should use the JDeveloper offline database object files.

What is the Offline Database

JDeveloper provides the tools you need to create and edit database objects, such as tables and constraints, outside the context of a database, using the offline Database model. You can create new tables and views, and generate the information to a database, or you can import database objects from a database schema, make the changes you want, and generate the changes back to the same database schema, to a new database schema, or to a file that you can run against a database at a later date.

Offline Database Model

The JDeveloper Offline database supports the following object types:

• Function
• Materialized View
• Materialized View Log
• Package
• Procedure
• Sequence
• Synonym
• Table
• Trigger
• Type
• View

Currently, JDeveloper offline DB objects do not support these objects:

• Queue
• Queue tables
• Policy
• Context

However, SXML persistence files for these object types can be imported using the applxdf extension.
JDeveloper provides tools to create and edit database objects such as tables, view etc. outside the context of a database. This tool called Offline Database Definition will be used to model physical database objects in Fusion applications. The migration tool will support migrating all user selected database objects defined in CASE to this offline database definition in JDeveloper along with SXML/XDF¹ deployment files.

Metadata SXML Comparison Tool^[3]

Offline table definitions can be version controlled and shared using a source control system. If you just create objects in the DB schema via the database navigator, you have nothing to source control. JDeveloper provides a comparison tool optimized for working with offline table definitions, which handles:

• The table data, properties, columns and constraints.
• The identity of objects, to track name changes.
• Checking for consistency, for example, ensuring:
  • That a column which is used in a key is not dropped.
  • That a constraint which uses an absent column is not added.
  • That a primary key column cannot be options.

Using this comparison tool, you can compare object metadata of the same type from different databases. This comparison depends on SXML. SXML is an XML representation which more closely maps to the SQL creation DDL. Two SXML documents of the same type can be compared and a new SXML document is provided which describes their differences.
Using this comparison tool, you're able to:

• Compare object definitions in different JDeveloper projects
  • Since the objects to be compared are in separate projects, you need to create a dependency between them to be able to perform this comparison.
• Compare versioned copies of DB objects
  • Versioning components allows you to browse through the historical changes of a component and make comparison between these versions. With JDEV, it's possible to compare different versions of database models.

Working on Data Modeling at Different Levels:

• UML class diagram
  • You can create a logical model using a UML class diagram to visually create or inspect classes, interfaces, attributes, operations, associations, inheritance relationships, and implementation relations and transform it to an Offline or Online Database definitions later.
  • See this tutorial for how-to.
  • You usually do logical modeling using a UML class model in the following steps:
    1. Preparing a class model diagram Environment
    2. Creating a Class Model Diagram
    3. Enhancing the Class Model
    4. Transform the Class Model into a Database Model
• Database diagram
  • You can follow [4, 5] to create new database diagram.
  • You can also drag tables, views, materialized views, synonyms, and sequences from a database schema onto a database diagram, where they become accessible as offline database objects.
• Offline Database
  • You can create new offline database objects, or capture them from a connection to a live database. After you have finished working with them, you can generate new and updated database definitions to online database schemas or to SQL scripts.
  • You can follow the instructions in [4, 5] to create new offline database objects. When you create an offline database, you choose the database emulation (for example, Oracle11g Database Release 1) the offline database should have.
  • You can also copy offline database objects to a project. In general, it is a good idea to make sure that the offline database uses the same database emulation as the source database.
  • Note that generation to a database is not certified against non-Oracle databases.

Notes

1. Prior to SXML migration, these were referred to as xdf (extension) files.

References

1. http://susanduncan.blogspot.com/
2. Database Development with JDeveloper
3. Metadata SXML Comparison Tool
4. Designing and Building Database Tables
5. Database Development with JDeveloper