27 Designing Databases Within Oracle JDeveloper

This chapter describes how to work with database objects in a database connection. It also contains information about working with offline databases in JDeveloper to create and edit offline database objects that can then be generated to a script database connection.

This chapter includes the following sections:

27.1 About Designing Databases Within Oracle JDeveloper

You can use JDeveloper to:

  • Create, edit, or delete database objects

  • Create, edit, or delete offline database objects

  • Work with offline versions of database definitions, and then generate those definitions to a file that is processed immediately or at a time you choose to create a table or other database objects via the database connection

  • Model offline databases, and model database objects in a live database connection on a diagram. For more information about modeling databases, see Section 23.5, "Database Diagram."

27.2 Creating, Editing, and Dropping Database Objects

You can create database objects and offline database definitions, you can edit those objects, and you can delete them or drop them a database connection.

27.2.1 Working with Offline Database Definitions

This section describes how to work with database objects, such as tables, views, constraints, outside the context of a database schema. Offline database is a technology in JDeveloper that allows you to create and edit database object definitions within a project, saved as .xml files, using the same editors that are used to create and edit database objects on live database connections.

You can create new offline database objects, or import them from a connection to a live database. After you have finished working with them, you can generate DDL that can be used to create and update database definitions in online database schemas.

The JDeveloper Offline database supports the following object types:

  • Function

  • Index

  • Materialized View

  • Materialized View Log

  • Package

  • Procedure

  • Sequence

  • Synonym

  • Table

  • Tablespace

  • Trigger

  • Type

  • View

For more information about Oracle Database support of any of these object types, see the Oracle Database SQL Language Reference.

Working with Offline Database Definitions

When you work with offline database definitions in JDeveloper, you work with objects that are stored as XML files, but which provide a model of objects in a live database connections. You can generate offline database definitions to live database connections to create, alter, or drop database objects.

JDeveloper provides the tools you need to create and edit database objects such as tables and constraints outside the context of a database. For example

  • You can create new tables and views and generate the information to a database.

  • You can create new tables and views and generate the information to a file, which you can edit and later run on a database connection.

  • You can import tables and views from a database schema, make the changes you want and then 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. JDeveloper allows you to manually reconcile changes before committing them to a database.

  • You can use the modeling tools in JDeveloper to visualize your offline database objects on a diagram. For more information about modeling databases, see Section 23.5, "Database Diagram."

How to Set Paths for Offline Database Files

You can configure a project's settings to specify the root locations for offline database objects available to that project. The database path is configured by default, so you only need to change it if you want to:

  • Include offline database objects that are stored in another project

  • Store new offline database object files somewhere else.

Offline database objects can be shared between projects by adding their file system location to the database path for a project. The order in which file system locations are entered in the database path signifies the order in which the directories are searched for offline database objects. The first location in the database path is the location in which new offline database object files are stored.

If you are modeling database objects, the model path (located on the Modelers preferences page in the Project Properties dialog) is used to specify the file location for the diagram.

Note:

When you are adding another database path to a project, you should save your work before proceeding. When you change the database path, the project reloads the offline database object definitions so any unsaved work for example, changes to tables, views, schemas or new objects that you have not yet saved, may be lost.

You can set a default root directory for database objects that will be used for all new projects.

To set the default root directory for database objects for new projects:

  1. Choose Application > Default Project Properties.

  2. Select Project > Source Paths > Offline Database, and enter the root directory.

You can change the database path for an existing project.

Setting the database path for an existing project:

  1. Right-click the project and choose Project Properties.

  2. Select Project > Source Paths > Offline Database, and enter the file system location for your project's offline database objects. Separate multiple file system locations using semicolons (;).

  3. You can selectively include and exclude subfolders using the Included and Excluded tabs. For more information, press F1 or click Help from within the dialog.

27.2.1.1 Offline Databases

JDeveloper works with offline database definitions in the context of offline databases that act as containers in a similar way to packages. In the Application Navigator, the offline database is shown below the Offline Database Sources node, shown in Figure 27-1.

Figure 27-1 Offline Database in the Application Navigator

Offline Database in the Application Navigator

In this case, a Java class and a database diagram have been created in the package project1, which is under the Application Sources node, and some offline database definitions have been created in an offline database called DATABASE1, which is under the Offline Database Sources node.

When you create an offline database, you choose the database emulation the offline database should have.

27.2.1.2 Configuring Offline Database Emulation

You can specify the type of database an offline database emulates. This determines the data types supported in the project.

The Oracle Database options available are:

  • Oracle 11g Database Release 1

  • MySQL Database Server 4.1.x

  • MySQL Database Server 5.x

  • Oracle 11g Database Release 2 (default)

  • Oracle Database 10g Release 1

  • Oracle Database 10g Release 2

  • Oracle Database 10g Express Edition Release 2

  • Oracle Database 11g Express Edition Release 2

  • Oracle Database 10g Lite Release 1

  • Oracle Database 10g Lite Release 3

  • Oracle 8i Server Release 3

  • Oracle 9i Database Release 2

  • TimesTen Database Server 11g

  • TimesTen Database Server 6.0

  • TimesTen Database Server 7.0

The non-Oracle database options available are:

  • Apache Derby 10.5

  • DB2 Universal Database 8.1

  • DB2 Universal Database 9.5

  • Generic JDBC Database

  • Informix Dynamic Server 10.0

  • Informix Dynamic Server 11.5

  • Microsoft SQL Server 2005

  • Microsoft SQL Server 2008

  • SQLite Database 3.6

  • Sybase Adaptive Server Enterprise 12.5

  • Sybase Adaptive Server Enterprise 15

27.2.1.3 How to Create Offline Databases

An offline database is a node in the Application Navigator that contains offline schemas and offline database object definitions.

To create an offline database:

  1. In the navigator, locate the project you want to work in.

  2. Right-click a project or anything in it, and choose New to display the New Gallery.

  3. From the New Gallery, expand Database Tier, and select Offline Database Objects, and select Offline Database.

  4. In the Create Offline Database dialog, enter a name for the offline database and choose the database type to emulate.

    For more information at any time, press F1 or click Help from within Create Offline Database dialog.

27.2.1.4 Offline Schemas

JDeveloper works with offline database definitions in the context of offline databases. Within the offline databases, schemas are the equivalent of schemas in live database connections. In the Application Navigator, the offline schema is shown below the Offline Database Sources node, illustrated in Figure 27-2.

Figure 27-2 Offline Schema in Application Navigator

Offline schema in Application Navigator

In this case, a Java class and a database diagram have been created in the package project1, which is under the Application Sources node, and some offline database definitions have been created in a schema called MYSCHEMA, which is in an offline database called DATABASE1 under the Offline Database Sources node.

27.2.1.5 How to Create Offline Schemas

To create an offline schema:

  1. In the navigator, locate the project you want to work in.

  2. Right-click a project or anything in it, and choose New to display the New Gallery.

  3. From the New Gallery, expand Database Tier, and select Offline Database Objects, and select Schema.

  4. In the Offline Database dialog, choose the offline database to create the schema in.

  5. In the Create Schema dialog, enter a name for the offline schema. For more information at any time, press F1 or click Help from within Create Schema dialog.

Context Menu Shortcut:

In the Application Navigator, right-click the offline database, and choose New Schema.

27.2.1.6 How to Create Offline Database Objects

You can create offline database objects from the New Gallery, or from the context menu of an offline database or offline schema in the Application Navigator. The process is similar regardless of the offline database object type.

About Tables

The types of tables that are available are:

  • Normal. This is a regular database table which can be partitioned. A partitioned table is a table that is organized into smaller and more manageable pieces called partitions. SQL queries and DML statements do not need to be modified in order to access partitioned tables; however, after partitions are defined, DDL statements can access and manipulate individual partitions rather than entire tables or indexes. Also, partitioning is entirely transparent to applications.

  • External. An external table is a read-only table whose metadata is stored in the database but whose data is stored outside the database. Among other capabilities, external tables enable you to query data without first loading it into the database.

  • Index Organized. An index-organized table is a table in which the rows, both primary key column values and non-key column values, are maintained in an index built on the primary key. Index-organized tables can be used to store index structures as tables in Oracle Database. Index-organized tables are best suited for primary key-based access and manipulation.

  • Temporary. The temporary table definition persists in the same way as the definition of a regular table, but the table segment and any data in the temporary table persist only for the duration of either the transaction or the session, and the table is not stored permanently in the database. Temporary tables cannot be partitioned or index organized.

Note:

You can only create and use relational table definitions in offline schemas, you cannot create and use object relational table definitions.

About Partitions

You can partition a table, an index, or a materialized view. A partitioned table or materialized view is a table or materialized view that is organized into smaller and more manageable pieces called partitions. SQL queries and DML statements do not need to be modified in order to access partitioned tables; however, after partitions are defined, DDL statements can access and manipulate individual partitions rather than entire tables or indexes. Also, partitioning is entirely transparent to applications.

Temporary tables cannot be partitioned.

A partitioned index consists of partitions containing an entry for each value that appears in the indexed column(s) of the table.

There are three types of partitions:

  • RANGE, which partitions the table on ranges of values from the column list. For an index-organized table this must be a subset of the primary key columns of the table.

  • HASH, which partitions the table using the hash method. Rows are assigned to partitions using a hash function on values found in columns designated as the partitioning key.

  • LIST, which partitions the table on lists of literal values from a column. This is useful for controlling how individual rows map to specific partitions.

If you have a RANGE partition on a table or index, you can additionally subpartition the table or index by HASH or LIST. Composite partitioning is a combination of two partitioning methods to further divide the data into subpartitions.

You can define subpartition templates which will be used in any partition for which you do not explicitly define subpartitions.

Note:

A table or index in Oracle Database which uses a hash partition by quantity will be displayed in JDeveloper as having individual hash partitions. You can either specify your individual partitions manually using the Create or Edit Table or Materialized View dialog, or define them by quantity and let the database do the work for you. Whichever way you choose to define your partitions, when you edit the database table or materialized view in JDeveloper, they will be displayed as individual partitions.

About Indexes

You can create indexes on columns in tables in order to speed up queries. Indexes provide faster access to data for operations that return a small portion of a table's rows. In general, you may want to create an index on a column in any of the following situations:

  • The column is queried frequently.

  • A referential integrity constraint exists on the column.

  • A unique key integrity constraint exists on the column.

You can create an index on any column; however, if the column is not used in any of these situations, creating an index on the column does not increase performance and the index takes up resources unnecessarily. Although the database creates an index for you on a column with an integrity constraint, explicitly creating an index on such a column is recommended. You can use the SQL Worksheet's execution plan to show a theoretical execution plan of a given query statement.

Indexes can be normal, where the index is either non-unique, unique, or bitmap, or they can be domain indexes.

In a non-unique normal index, the index can contain multiple identical values. In a unique normal index, no duplicate values are permitted. Use a unique normal index when values are unique in the column. In a bitmap normal index, rowids associated with a key value are stored as a bitmap. These are useful for systems in which data is not frequently updated by many concurrent systems, or where there is a small range of values.

Domain indexes are user-defined indexes, each of which indexes data in an application-specific domain. They are built using the indexing logic supplied by a user-defined indextype. An indextype provides an efficient mechanism to access data that satisfy certain operator predicates. Typically, the user-defined indextype is part of an Oracle option, like the Spatial option.

Working with User-Defined Data Types

JDeveloper allows you to define your own data types, which can be either object types and collection types.

Object types are abstractions of the real-world entities—for example, purchase orders—that application programs deal with. An object type is a schema object with three kinds of components:

  • A name, which serves to identify the object type uniquely within that schema.

  • Attributes, which model the structure and state of the real world entity. Attributes are built-in types or other user-defined types.

  • Methods, which are functions or procedures written in PL/SQL and stored in the database, or written in a language like C and stored externally. Methods implement operations the application can perform on the real world entity.

An object type is a template. A structured data unit that matches the template is called an object.

JDeveloper allows you to create an object type specification, or an object type specification and body.

When you create a new object type spec, it is similar to

TYPE TYPE1 AS OBJECT (a null );

When you create an object type body, it is similar to

CREATE TYPE TYPE1 AS VARRAY(1) OF null;
/

Collection types are different. Each collection type describes a data unit made up of an indefinite number of elements, all of the same data type. The collection types are array types and table types.

Array types and table types are schema objects. The corresponding data units are called VARRAYs and nested tables. When there is no danger of confusion, we often refer to the collection types as VARRAYs and nested tables.

Collection types have constructor methods. The name of the constructor method is the name of the type, and its argument is a comma-separated list of the new collection's elements. The constructor method is a function. It returns the new collection as its value.

An expression consisting of the type name followed by empty parentheses represents a call to the constructor method to create an empty collection of that type. An empty collection is different from a null collection.

JDeveloper allows you to create array types and table types.

n array type is similar to

TYPE TYPE1 AS VARRAY(1) OF null;

A table type is similar to

TYPE TYPE1 AS TABLE OF null; 

Note:

In order to use data types when the project is configured for database emulation other than Oracle Database, the database it emulates must support type creation.

About Materialized Views

Materialized views are database objects that contain the results of a query. The FROM clause of the query can name tables, views, and other materialized views. You can model, create, and edit materialized views in a live database connection, and offline materialized views in an offline database in JDeveloper.

When importing materialized views from Oracle Database to a JDeveloper project:

  • If a materialized view on the database specifies WITHOUT REDUCED PRECISION, when it is imported into JDeveloper it will use reduced precision, and the Reduced Precision option on the Properties page of the Edit Materialized View dialog is selected. If it is important that the materialized view does not reduce precision, select No Reduced Precision in the dialog.

  • If a materialized view on the database specifies USING ROLLBACK SEGMENT and USING TRUSTED CONSTRAINTS, when it is imported into JDeveloper no rollback segment is selected on the Properties page of the Edit Materialized View dialog, and the constraint is shown as Enforced. If necessary, change the options in the Edit Materialized View dialog

Validating Date and Time Values

When you create offline table definitions or tables in a database and use date and time default values, JDeveloper validates these values. For a date, you can use:

  • Oracle date functions

  • A quoted string of the form DD-MON-RR, where:

    • The month can be spelled out in full.

    • The year can be written in full, e.g., 2011.

    • The separators (-) can be absent, or any non-alphanumeric character combined with spaces.

For a time stamp, you can use a quoted string of the form DD-MON-RR HH.MI.SSXFF AM TZR, where:

  • The month can be spelled out in full.

  • The year can be written in full, e.g., 2011.

  • The hours and minutes must be present.

  • Seconds, fractions of second, AM/PM, and time zone are optional.

  • The separators (-) can be absent, or any non-alphanumeric character combined with spaces.

When you import tables from a database, date and time values are validated according to the rules above. If the validation prevents you from importing a table from Oracle Database, you can turn it off.

To turn off date and time validation:

  1. Choose Tools > Preferences > Database Connections.

  2. Uncheck Validate date and time default values.

To create an offline type definition:

  1. In the Application Navigator, expand the workspace and project you want to work in.

  2. Right-click a project or anything in it, or an offline schema or anything in it, and choose New to display the New Gallery.

  3. From the New Gallery, expand Database Tier, and select Offline Database Objects.

  4. Select Type to launch the Create Offline Type dialog.

  5. Enter parameters and select options to define the type.

    For more information at any time, press F1 or click Help from within the Create Offline Type dialog.

Context Menu Shortcut:

In the Application Navigator, right-click the offline schema, select New Database Object, then select New Type.

You can edit user-defined types by double-clicking the type in the Application Navigator. The SQL comprising the type opens in the source editor.

To create offline database object definitions:

Note:

You can only create and use relational table definitions in offline schemas, you cannot create and use object relational table definitions.

  1. n the Application Navigator, right-click a project or anything in it, and choose New to display the New Gallery.

  2. From the New Gallery, expand Database Tier, and select Offline Database Objects.

  3. Select the offline database object you want to create to launch the Create dialog or wizard.

    For more information at any time, press F1 or click Help from within the Create Offline Type dialog.

Context Menu Shortcut:

In the Application Navigator, right-click the offline schema, select New Database Object, then select object you want to create.

To drop an offline database definition:

  1. In the navigator, expand the project, offline database, and offline schema containing the offline object. Right-click the offline object, and choose Delete.

  2. Right-click the offline object, and choose Delete.

    Alternatively, right-click the offline table and choose File > Delete.

    Note:

    If the offline table has any dependencies, the Confirm Delete dialog warns you and allows you to see the usages. If you still choose to delete the offline table, the Cascade Confirm Delete dialog warns you which objects will also be deleted.

27.2.1.7 How to Import Offline Database Definitions Based on Database Objects

You can drag tables, views, materialized views, synonyms, and sequences from a database schema onto a database diagram, where they become accessible as offline database objects.

Another way of working with database objects as offline database objects is to import them from a database to an offline database.

To drag objects onto a database diagram:

  1. Create a new database diagram.

    Alternatively, open an existing diagram.

  2. Choose the database connection. Go to either:

    • View > Database > Database Navigator.

    • Application Resources in the Application Navigator.

    Expand IDE Connections or application, and select a database connection.

  3. In the connection, expand the schema and expand the node you want: Tables, Views, Materialized Views, Sequences, or Synonyms.

  4. Select the object you want to model, and drag it onto the database diagram. This opens the Specify Location dialog. Ensure that Copy Objects to Project is selected, and click OK. The object is now displayed on the diagram and listed in the Application Navigator.

You can drag more than one object of the same type onto a database diagram, by holding down the Ctrl key as you select them.

Note:

If you import the same object more than once a warning message is displayed. If you are using Copy to Project and choose to proceed, you can replace or delete the existing object. If you drag a database object onto the diagram and choose to proceed the new object overwrites the existing one.

How to Copy Database Objects and Offline Database Definitions to Projects

You can copy database objects from a database schema to an offline database where they become available as offline database objects. You can also copy offline database objects to a project.

If you try to copy database objects to an offline database which emulates a different database version you will see an error message giving you guidance on how to proceed. In general, it is a good idea to make sure that the offline database uses the same database emulation as the source database.

You can apply filters in the wizard to only display the objects you are interested in, and when there are a large number of objects in the schema you can turn off auto-query so that the wizard does not refresh every time you type a filter character.

You can apply filters to select the objects that are displayed as available for import. In the Object Picker page (step 3 of the wizard), you can:

  • Enter characters in Name Filter to filter the list of available objects by name. Name Filter is case sensitive.

  • When there are a large number of objects, you can turn off Auto-Query, and click Query after you have entered the filter you want to use.

To import database objects:

  1. In the Application Navigator, select the project you want to work in.

  2. Choose File > New to open the New Gallery.

  3. In the Categories tree, expand Database Tier and select Offline Database Objects.

  4. In the Items list, double-click Copy Database Objects to a Project to launch the Copy Database Objects to a Project wizard.

    For more information at any time, press F1 or click Help from within the wizard.

27.2.1.8 Offline Tables and Foreign Keys

When you import a table from a live database schema to JDeveloper, information about any foreign keys will not necessarily be available. The following sections discuss how foreign key information is treated in different cases.

Importing Tables at Both Ends of a Foreign Key

This is the simplest case. When JDeveloper imports tables that have foreign keys between them, information about the foreign key is also imported. Therefore the foreign key is correctly shown on a database diagram and on the Constraint Information page of the Edit Offline Table dialog.

After you have finished working on the tables you can choose to generate your changes directly back to the database.

Importing a Table at One End of a Foreign Key

In the case that JDeveloper imports a table that has a foreign key, but the table at the other end of the key is not imported, an unvalidated foreign key reference (called a name-based foreign key) is used. The foreign key is displayed in the Constraints compartment of the owning table, but not as association lines on the database diagram. The table dialog will show the foreign key as broken. The name-based foreign key is not validated, but the DDL is correct so that changes can be generated directly to a database.

Best Practice

From the information above you can see that if you are importing tables to act as the basis of a new database schema, then you do not need to worry about foreign keys to tables that you are not interested in. You can safely make your changes and generate the online tables in a new schema.

However if you are importing tables so that you can make the changes you want and then generate the changes back to the same database schema you should either:

  • Import all tables that have a foreign key relationship, whether you intend to change them or not, so that you generate the correct information about the foreign keys to a SQL file or directly to the database. The Choose Operation page of the Copy Database Objects to a Project wizard allows you to import dependencies.

  • Import just the tables you want to change and rely on the name-based foreign keys to hold the information

27.2.1.9 How to Refresh Offline Database Objects

You can refresh any imported offline object from the database connection it was originally imported from. Note that if you reimport the object, you will lose any changes you have made in the offline object.

Note:

You cannot refresh an object that was created as an offline object in JDeveloper and then generated to the database. If you make changes to the object in the database and want those changes to be reflected in the offline object, you must import the object from the database and overwrite the offline object by selecting Automatically replace existing objects on the Select Target Schema page, or selecting Yes on the Confirm Overwrite message at the end of the wizard.

To reimport an object from a database connection:

  1. Right-click the offline object in the Application Navigator, and choose Refresh from db-connection.

  2. When the Confirm Offline Object Overwrite dialog appears, check that you want to reimport the object and then click Yes. Otherwise click No. This may take a few seconds.

27.2.1.10 How to Create Objects from Templates

You can create offline database objects based on templates, for example: To use a default set of storage options for all tables created. To use a default set of user property values for all tables created. To use a set of default columns for all tables created. A template table can create a default primary key, a column sequence, and a trigger.

When you create a new object using the template, the properties that are set on the template are copied to the new object and therefore pre-populate the options in the create dialog. When the namespace of owned objects is not the parent object, the name must be unique within the schema, not just within the parent object. For example, Index and Constraint names must be unique within the schema, not just within the owning Table, Materialized View, or View.

How to Create Offline Templates

You can create offline database objects from templates.

To create default templates for an offline database:

  1. Create a new offline database.

  2. In the Create Offline Database dialog, select Initialize Default Templates. When you click OK, the offline database is created, along with default template objects which have the name TEMPLATE_object. You can edit the template database objects by right-clicking the one you want and choosing Properties, which opens the Edit object dialog.

To edit the default templates for a new offline database:

  1. If you are creating a new offline database, select Edit Default Templates in the Create Offline Database dialog.

  2. When you click OK, the Default Templates dialog opens where you can edit the default template for each object type. For more information at any time, press F1 or click Help from within the Default Templates dialog.

To edit the default templates for an existing offline database:

  1. In the Application Navigator, right-click the offline database and choose Properties.

  2. Navigate to the Default Templates page of the dialog, where you can edit the default template for each object type. For more information at any time, press F1 or click Help from within the Edit Offline Database dialog.

How to Create Offline Database Objects from Templates

You can create offline database objects from templates as offline database objects in the Application Navigator, or as modeled offline database objects on a database diagram.

Before creating offline database objects based on templates, you first need to create the templates.

To create an offline database object based on a template:

  1. In the navigator, right-click a project or anything in it, and choose New to display the New Gallery.

  2. From the New Gallery, expand Database Tier, and select Offline Database Objects.

  3. Select Database Object from Template to launch the Choose Template Object dialog. For more information at any time, press F1 or click Help from within the Create object dialog.

  4. Choose the object type to create from a template. When you click OK, the Create object dialog opens, pre populated with the values in the template. For more information at any time, press F1 or click Help from within the Create object dialog.

    When you click OK the object is created and listed in the Application Navigator under the offline database and schema.

You can also open the Choose Template Object dialog from the context menu of an offline database, or any node under it, in the Application Navigator.

To quickly create an offline database object based on a template:

  1. Right-click the offline database node in the Application Navigator, or any node under it, and choose New Database Object > From Template.

To quickly create an offline database object based on another offline database object:

  1. In the Application Navigator, right-click the offline database object you want to base a new object on and choose Use as Template.

  2. The Edit dialog for the type of object opens, pre populated with the properties of the source object.

27.2.1.11 Working with User Property Libraries

You can add user defined properties to database objects. For an instance of a database object; these user defined properties can be assigned specific values.

You can work with libraries that can limit the properties you can define for an offline database object. For example, you can determine that all tables must have a column of a particular type, or that only certain values are allowed, or that a property is mandatory.

User property libraries are defined in the context of an offline database. First you have to define user properties for the types of offline database objects you want to use. Then you can use the libraries when creating offline database objects in the offline database that the library is defined for.

User property libraries can contain properties defined for:

  • Tables, columns, constraints, indexes

  • Functions, packages, procedures

  • Materialized Views

  • Materialized View Logs

  • Sequences

  • Synonyms

  • Tablespaces

  • Triggers

  • Types

  • Views

27.2.1.11.1 How to Create and Edit User Property Libraries

User property libraries are independent of offline databases, but can be added to them using the offline database edit dialog.

To create or use a user property library:

  1. In the Application Navigator, expand the project, right-click the offline database and choose Properties.

  2. In the Edit Offline Database dialog, choose User Property Libraries.

  3. You can:

    • Create a new library. In this case, you enter a filename and location for the library.

    • Add a library that exists in the file system. In this case, you browse to the library location on the file system.

    • Edit an existing library by selecting it from the list.

  4. Enter values for the user properties in the Edit User Property Library File dialog. For more information at any time, click F1 or press Help in the dialog.

Once you have created a user property library for an offline database object type, you can use it to store user property value specific to that instance of the object.

You can provide validation for the user defined property value to validate the database objects by writing your own validation code. This is an advanced procedure which is outside the scope of this user guide. For more information see UserPropertyValidationManager in Oracle Fusion Middleware Java API Reference for Oracle Extension SDK. For information about using the Extension SDK, see Oracle Fusion Middleware Developer's Guide for Oracle JDeveloper Extensions.

27.2.1.11.2 How to Use User Property Libraries

Use user properties for database objects

Before you can use user properties in offline database objects, you must define one or more user property libraries for the offline database you are working in.

To use user properties in an offline database object:

  1. Create the offline database object.

  2. Navigate to the User Properties page or tab of the offline database object dialog, and enter values for the user properties.

27.2.1.12 How to Generate Offline Database Objects to the Database

The Generate SQL from Offline Database Objects wizard allows you to choose how to update a database schema with the offline objects that you have created or edited. You can:

  • Create or replace the objects in the database.

    If you choose to generate a SQL file, it will contain CREATE and DROP statements.

  • Update existing database schema objects with the changes you have made to the offline database objects. JDeveloper first reconciles the offline database definitions against the objects in the database schema to identify the changes necessary. You can choose to do a manual reconcile and select only some of the changes.

    If you choose to generate a SQL file, it will contain ALTER statements.

Whether you are generating changes to a database, or reconciling changes, you can choose to:

  • Generate a SQL file that you can examine, and run against the database later.

  • Make the changes directly to the database.

  • Make the changes to the database and also generate a SQL file.

Alternatively, if you just want to generate one or more offline tables back to the database connection they were originally imported from, you can do this directly from the navigator.

Note:

If you have made changes to tables that have foreign keys, it is possible that the foreign keys will be dropped when you generate your changes to the database.

27.2.1.12.1 Reconciliation issues

This section contains information about problems you may come across when reconciling.

27.2.1.12.2 Cannot modify constraints

Constraints can be created or dropped during reconciliation; they cannot be modified. The only ALTER TABLE reconciliations that can be performed are ADD CONSTRAINT, DROP CONSTRAINT, ADD COLUMN, DROP COLUMN, and WIDEN COLUMN.

27.2.1.12.3 Cannot reconcile renamed tables

You can change the name of a table when you import it or while you are editing it offline. If you try to reconcile the renamed table back to the database, you will receive an error message because the database does not have a record of the table with its new name.

To avoid this, create the renamed table in the database, do not reconcile or replace it.

How to Generate Database Definitions to a Database

27.2.1.12.4 How to Generate Database Definitions to a File

Create a SQL file containing the CREATE and DROP statements that you can run against an online database schema.

Note:

If you have made changes to tables that have foreign keys, it is possible that the foreign keys will be dropped when you generate your changes to the database.

If you have one or more offline database definitions containing information that you want to generate to a database, you can use this method. However if you want to quickly generate one or more offline database definitions back to their original database connection, you can do this from the navigator.

When you have an offline version of an online database table, JDeveloper keeps track of the information comprising the offline database table columns behind the scenes. When the database is updated outside JDeveloper, for example when the generated SQL script is run in a SQL session, or when another user updates the database, JDeveloper cannot track the link between the offline database table and the table in the database. To get around this, you must refresh the offline schema objects from the database.

To create the file:

  1. In the Application Navigator, expand the application and project.

  2. Right-click an offline schema and choose New to display the New Gallery.

  3. From the New Gallery, expand Database Tier, and select Offline Database Objects.

  4. Select SQL Generated from Offline Database Objects to launch the Generate SQL from Offline Database Objects wizard.

  5. On the page specify details for the generated file, then click Next.

  6. On the Finish page, click Finish to create the file.

Context Menu Shortcut:

In the navigator, right-click one or more offline database definitions and choose Generate to

or On the database diagram select one or more modeled database definitions, right-click and choose Synchronize with Database > Generate To.

27.2.1.13 Renaming Offline Database Objects

JDeveloper has a limited ability to keep track of renamed offline database objects such as tables and sub objects such as columns or constraints. In some circumstances JDeveloper will drop the database object with the unchanged name and create a new database object with the new name, which can lead to loss of data. You need to be aware of the situations when this can arise so as to avoid them.

This can occur when an offline database object is generated to a database connection. If you then change the name of the offline database object or of a sub object such as a column or index, and then generate the changed offline database object to a database connection, in the database the object with the original name is dropped and a new object using the new name is created.

A different situation which can lead to loss of data is when an object is imported from a database connection, then the name of an offline database sub object is changed. In this case, the first time you generate to a database connection the database sub object is correctly updated. However if you attempt to generate to the database connection a second time the sub object with the original name is dropped and a new database sub object with the new name is created. The reason that this happens is because Copy to Project uses the original name in an internal reference to the online sub object.

27.2.1.14 Using Offline Database Reports

JDeveloper provides many reports about an offline database and its objects. You can also create your own user-defined reports for offline database objects.

27.2.1.14.1 Offline Database Reports

JDeveloper comes with a set of pre-built report definitions, and you can also define your own report definitions.

You can use the pre-built reports directly to provide information about an offline database, or you can alter them to create a report tailored to your specific requirements.

Once you have created a pre-built report, you can examine the SQL that makes up the query for the report, and if necessary change it. You can also set parameters in the report query that are called when the report is run.

27.2.1.14.2 How to Use Pre-built Reports

The pre-built reports quickly provide useful reports which provide the following queries for an offline database:

  • OBJECT_COUNT, which lists the number of schema objects of each object type.

  • OBJECT_LIST, which lists all schema objects in the offline database.

  • TABLE_COLUMNS, which lists all tables with their column information.

  • TABLE_COLUMN_COUNT, which lists all tables with their column count.

  • TABLE_NO_PKS, which displays all tables that do not have a primary key.

When you run the Pre-Built Reports wizard, a separate file is generated for each of the pre-built reports that you choose to the location that you specified, and the file is listed in the Application Navigator under Resources.

You can examine the query for one of the pre-built reports by creating a new offline database report definition, and basing it on the pre-built report. You can also create a new report based on a pre-built report.

Note:

If you specify a location that is outside the current project the reports are generated, but they are not listed in the Application Navigator. The files have the file name pre-built-report.report, and they are structured as XML files.

How to use predefined reports:

  1. In the Application Navigator, expand the application and project.

  2. Right-click an offline database and choose New to display the New Gallery.

  3. From the New Gallery, expand Database Tier, and select Offline Database Objects.

  4. Select Pre-Built Reports to launch the Pre-Built Reports wizard.

  5. Choose the reports you want to generate and if necessary click Next to change the offline database that you want to run the report on.

  6. Click Finish. The reports you have chosen are listed in the Application Navigator under the offline database node.

To edit a predefined report:

  1. In the Application Navigator, expand the application, project and offline database.

  2. Right-click the report and choose Properties to open the Edit Report dialog, where you can examine and change the properties.

  3. On the Report Definition page, change the name of the report. Change other details as required, for example, you can change the offline database that the report is to run on.

  4. To change the SQL query for the report, either change the SQL on the Query Definition page, or expand the Query Definition node and declaratively define the SQL query. You can use the Check Syntax button on the Query Definition page to check that the SQL parses.

  5. To add parameters to the query, use the Report Parameters page.

  6. To change the format that the report is published in, use the Publish Report page.

To run a pre-built report:

  • In the Application Navigator, right-click the report and choose Run. The report is run against the offline database you specified. The results are either displayed in the Reports Log window (default), or in the location and format that you have chosen in the Publish Report page of the Edit Report dialog.

27.2.1.14.3 How to Define Report Definitions

You can define your own report definitions. You can either define a query from scratch, or you can base the new report definition on an existing report or on one of the pre-built reports.

You can specify that just the report definition is produced, or you can specify that when the report definition is run a comma-separated file is produced, or that a formatted HTML document is produced.

If you choose to generate an HTML document, you can optionally specify that a CSS file is used, and you can edit the default boilerplate text that formats the body of the HTML document.

How to create a report:

  1. In the Application Navigator, expand the application and project.

  2. Right-click an offline database and choose New to display the New Gallery.

  3. From the New Gallery, expand Database Tier, and select Offline Database Objects.

  4. Select Reports to launch the Create Report wizard.

  5. Enter a name for the report, and choose whether to copy report details from a pre-built report, from an existing report, or whether to create a new report from scratch.

  6. Change the offline database the report is to run on in the Offline Database page.

  7. Create or examine the SQL query for the report in the Query Definition page. You can use the pages under the Query Definition node to declaratively create the SQL Query.

  8. If you want to use parameters with the report, enter them in the Report Parameters page.

  9. Click Finish. The new report is listed in the Application Navigator under the offline database node.

To run a report:

  • In the Application Navigator, right-click the report and choose Run. The report is run against the offline database you specified. The results are either displayed in the Reports Log window (default), or in the location and format that you have chosen in the Publish Report page of the Edit Report dialog.

27.2.1.14.4 How to Use Boilerplate Text with HTML Reports

JDeveloper provides some boiler-plate code to help you to format the report, and it includes three new HTML tags:

  • <report/>, which defines the report output.

  • <query/>, which defines the text of the query used to generate the report.

  • <rows/>, which is the number of rows in the report.

The boiler-plate code provided is:

Example 27-1 Boiler-plate code for User-Defined Reports

<h1>Table Report</h1>
<p>Query used:</p>
<pre><query/></pre>
<p>The report output is:</p>
<report/>
<p>Report complete. <rows/> row(s) returned.</p>

You can edit this in the Create or Edit Report dialog to customize the report.

27.2.1.14.5 How to Edit User-Defined Reports

You can change the properties of defined reports:

  • Change the name of the report, or the directory where it is stored.

  • Change the database connection you want to use.

  • Use parameters to define a query for the report.

  • Choose the format that the report should be published in.

To edit a report:

  1. In the Application Navigator, expand the application, project and offline database.

  2. Right-click the report and choose Properties to open the Edit Report dialog, where you can examine and change the properties.

27.2.1.15 Transforming from a UML Model

You can transform a UML Class model to an offline database model using the Offline Database Objects from UML Class Model wizard. For more information, see Section 22.7.1, "How to Transform UML and Offline Databases."

27.2.1.16 Working with Offline Database Objects in Source Control Systems

Developer provides a number of features for developing in teams, including several version control software systems. These are described in Chapter 6, "Versioning Applications with Source Control".

Offline table definitions can be version controlled and shared using a source control system. JDeveloper provides a compare tool optimized for working with offline table definitions:

  • You can compare any offline db object. You can either compare with previous version, or get a full version history and compare any two versions.

  • You can track name changes and the identity of objects.

  • You can check 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 optional.

    Note:

    While you can only compare versions of offline database objects using a source control system, for example to see the dependency of a constraint on a column, you can manually reconcile changes before committing them to a database using the Generate SQL from Offline Database Objects wizard. For more information, see Section 27.2.1.12, "How to Generate Offline Database Objects to the Database."

27.2.2 Working with Database Objects

You can create database objects in a database connection in the Database Navigator.

You must have a database connection in order to create database objects, and the user name used to create that connection must have the privilege to create the database object, either by having been granted the appropriate privileges (CREATE, DROP, and so on) or having been granted a role such as administrator that contains the privilege.

For more information about Oracle Database objects, see the Oracle Database SQL Language Reference.

To create a database object in the Database Navigator:

  1. If necessary, choose View > Database > Database Navigator.

  2. Expand IDE Connections or application, and expand the database connection.

  3. Navigate to the node for the database object type you want to create. Right-click and choose New object from the context menu.

    Alternatively, click File > New to open the New Gallery. In the New Gallery, expand Database Tier, and select Database Objects. Select the offline database object type you want to create to launch the Create dialog or wizard.

  4. Complete the Create object dialog.

    For more information at any time, press F1 or click Help from within the Create object dialog.

To edit a database object:

  1. If necessary, choose View > Database > Database Navigator.

  2. Expand IDE Connections or application, and expand the database connection, and navigate to the node and database object you want to edit.

  3. Right-click and choose Edit to open the Edit object dialog.

    For more information at any time, press F1 or click Help from within the Create object dialog.

To drop a database object:

  1. If necessary, choose View > Database > Database Navigator.

  2. Expand IDE Connections or application, and expand the database connection, and navigate to the node and database object you want to drop.

  3. Right-click and choose Drop.

27.2.3 Using Database Reports

JDeveloper provides a number of predefined reports about the database and its objects. You can also create your own user-defined database reports.

Database reports that query the database for latest information are run from the Database Reports Navigator. For more information, see Section 25.3, "Using the Database Reports Navigator."

27.3 Creating Scripts from Offline and Database Objects

You can generate database objects and offline database definitions to SQL scripts. You can also generate tables to Oracle MetaBase (OMB) scripts, which can be imported into Oracle Warehouse Builder.

27.3.1 How to Create SQL Scripts

You can create SQL scripts from offline database definitions in the Application Navigator, or from database objects in the Database Navigator.

To create a SQL script from a database object in the Database Navigator or from an offline database object in the Application Navigator:

  1. Either choose View > Database > Database Navigator, expand the database connection and schema, and right-click the database object you want to create the script from

    or

    Choose View > Application Navigator, navigate to the offline database definition you want to create the script from.

  2. Choose Generate to SQL script.

  3. The Generate SQL from Database Objects dialog opens at the Choose Operation page. For more information at any time, press F1 or click Help in the dialog.

  4. Complete the dialog. The script is created and opened in the SQL Worksheet.

27.3.2 How to Create OMB Scripts from Tables

You can create scripts formatted as Oracle MetaBase (OMB) scripts for Oracle Warehouse Builder from offline tables in the Application Navigator.

The default name of the script is omb_scriptn.tcl, and it is generated into the current project and appears in the navigator under the same offline database as the tables from which is it generated.

To create a OMB script from a table in the Application Navigator:

  1. Choose View > Application Navigator, navigate to the offline table or tables you want to create the script from.

  2. Choose Generate to OMB script. The script is created and opened in the source editor.