26 Designing Databases Within Oracle JDeveloper
This chapter includes the following sections:
26.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 figgeroisd 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 Modeling with Database Diagrams .
26.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 from a database connection.
26.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. Alternatively, you can have a connection to a live database and reverse engineer database objects. 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 (as part of a table)
Database Link
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 Reference.
How to Work 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 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 reverse engineer 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 Modeling with Database Diagrams .
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:
Choose Application > Default Project Properties.
Select Project > Source Paths > Offline Database, and enter the root directory.
You can change the database path for an existing project.
To set the database path for an existing project:
Right-click the project and choose Project Properties.
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 (
;).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.
26.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 Applications window, the offline database is shown below the Offline Database Sources node, shown in Figure 26-1.
Figure 26-1 Offline Database in the Applications Window
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 of the offline database.
26.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.
For information about which database versions are compatible with JDeveloper, see the JDeveloper Certification Information at http://www.oracle.com/technetwork/developer-tools/jdev/documentation/index.html
26.2.1.3 How to Create Offline Databases
An offline database is a node in the Applications window that contains offline schemas and offline database object definitions.
To create an offline database:
26.2.1.4 Offline Schemas
JDeveloper works with offline database definitions in the context of offline databases. Within the offline databases, offline schemas are the equivalent of schemas (or users) in live database connections. In the Applications window, the offline schema is shown below the Offline Database Sources node, illustrated in Figure 26-2.
Figure 26-2 Offline Schema in Applications Window
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 SCHEMA1, which is in an offline database called DATABASE1 under the Offline Database Sources node.
26.2.1.5 How to Create Offline Schemas
To create an offline schema:
- In the Applications window, locate the project you want to work in.
- Right-click a project or anything in it, and choose New to display the New Gallery.
- From the New Gallery, expand Database Tier, and select Offline Database Objects, and select Schema.
- In the Offline Database dialog, choose the offline database to create the schema in.
- 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.
Example 26-1 Context Menu Shortcut:
In the Applications window, right-click the offline database, and choose New Schema.
26.2.1.6 How to Create Offline Database Objects
There are a number of ways that you can create offline database objects:
You can always create offline database objects from the New Gallery.
You can always create a database diagram, a table or a view from the context menu of a project configured for offline database development, or from a node under that project.
Once you have created an offline database object, you can create another from the context menu of a project configured for offline database development, or from a node under that project.
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.
You can combine two partitioning methods, called composite partitioning, to further divide the data into subpartitions. Composite partitioning is supported for:
Range-Range
Range-Hash
Range-List
List-Range
List-Hash
List-List
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.
Index types are 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 or 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 BODY 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.
An 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 reverse engineering materialized views from Oracle Database to a JDeveloper project:
If a materialized view on the database specifies WITHOUT REDUCED PRECISION, when it is reverse engineered 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 reverse engineered 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
To create an offline type definition:
In the Applications window, expand the application and project you want to work in.
Right-click a project or a node under it such as an offline schema, and choose New > From Gallery to display the New Gallery.
From the New Gallery, expand Database Tier, and select Offline Database Objects.
Select Type to launch the Create Offline Type dialog.
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.
You can edit user-defined types by double-clicking the type in the Applications window. 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.
In the Applications window, right-click a project or anything in it, and choose New to display the New Gallery.
From the New Gallery, expand Database Tier, and select Offline Database Objects.
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 Applications window, right-click the offline schema, select New Database Object, then select object you want to create.
To drop an offline database definition:
In the Applications window, expand the project, offline database, and offline schema containing the offline object. 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.
26.2.1.7 How Reverse Engineer Database Definitions Based on Database Objects
You can drag tables, views, materialized views, synonyms, and sequences from an online database schema onto a database diagram, where they become accessible as offline database objects.
To drag objects onto a database diagram:
Create a new database diagram.
Alternatively, open an existing diagram.
Choose the database connection. Go to either:
Window > Database > Databases window.
Application Resources in the Applications window.
Expand IDE Connections or application, and select a database connection.
In the connection, expand the schema and expand the node you want: Tables, Views, Materialized Views, Sequences, or Synonyms.
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 Applications window.
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 reverse engineer 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 Reverse Engineer Database Objects and Offline Database Definitions to Projects
You can reverse engineer 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 reverse engineer 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 reverse engineering. 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 reverse engineer database objects:
26.2.1.8 Offline Tables and Foreign Keys
When you reverse engineer 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.
Reverse Engineering Tables at Both Ends of a Foreign Key
This is the simplest case. When JDeveloper reverse engineers tables that have foreign keys between them, information about the foreign key is also reverse engineered. 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.
Best Practice
From the information above you can see that if you are reverse engineering 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 reverse engineering tables so that you can make the changes you want and then generate the changes back to the same database schema you should reverse engineer 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 Specify Operation page of the Offline Database Objects from Source Database wizard allows you to reverse engineer dependencies.
26.2.1.9 How to Refresh Offline Database Objects
You can refresh any reverse engineered offline object from the database connection it was originally reverse engineered from. Note that if you reverse engineer 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 reverse engineer the object from the database and overwrite the offline object by selecting the Replace on the Specify Operation page.
To reverse engineer an object from a database connection a second time:
- Right-click the offline object in the Applications window, and choose Refresh from db-connection.
- When the Confirm Offline Object Overwrite dialog appears, check that you want to reverse engineer the object and then click Yes. Otherwise click No. This may take a few seconds.
26.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:
Create a new offline database.
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 an existing offline database:
In the Applications window, navigate to the template object and choose
Openfrom the context menu. The template object opens in the appropriate editor where you an edit it.
How to Create Offline Database Objects from Templates
You can create offline database objects from templates as offline database objects in the Applications window, 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:
26.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 user property libraries when creating offline database objects.
User property libraries can contain properties defined for:
Tables, columns, constraints, indexes
Database links
Functions, packages, procedures
Materialized Views
Materialized View Logs
Sequences
Synonyms
Tablespaces
Triggers
Types
Views
26.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:
Once you have created a user property library for an offline database object type, you can use it to store user property values.
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 Java API Reference for Oracle Extension SDK. For information about using the Extension SDK, see Developing Extensions for Oracle JDeveloper.
26.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 the user property libraries.
To use user properties in an offline database object:
- Create the offline database object.
- Navigate to the User Properties page or tab of the offline database object dialog, and enter values for the user properties.
26.2.1.12 How to Generate Offline Database Objects to the Database
The Generate SQL from 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.
Alternatively, if you just want to generate one or more offline tables back to the database connection they were originally reverse engineered from, you can do this directly from the Applications window.
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.
26.2.1.12.1 Reconciliation issues
This section contains information about problems you may come across when reconciling.
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.
Cannot reconcile renamed tables
You can change the name of a table when you reverse engineer 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.
26.2.1.12.2 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 Applications window.
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:
- In the Applications window, expand the application and project.
- Right-click an offline schema and choose New to display the New Gallery.
- From the New Gallery, expand Database Tier, and select Database Files.
- Select SQL File from Source Database to launch the Generate SQL from Database Objects wizard.
- On the page specify details for the generated file, then click Next.
- On the Finish page, click Finish to create the file.
Context Menu Shortcut:
In the Applications window, 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.
26.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 reverse engineered 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.
26.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.
26.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.
26.2.1.14.1.1 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 Applications window under Resources.
Note:
If you specify a location that is outside the current project the reports are generated, but they are not listed in the Applications window. The files have the file name pre-built-report.report, and they are structured as XML files.
How to use predefined reports:
In the Applications window, expand the application and project.
Right-click an offline database and choose New to display the New Gallery.
From the New Gallery, expand Database Tier, and select Offline Database Objects.
Select Pre-Built Reports to launch the Pre-Built Reports wizard.
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.
Click Finish. The reports you have chosen are listed in the Applications window under the offline database node.
To edit a predefined report:
- In the Applications window, expand the application, project and offline database.
- Right-click the report and choose Properties to open the Edit Report dialog, where you can examine and change the properties.
- 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.
- 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.
- To add parameters to the query, use the Report Parameters page.
- To change the format that the report is published in, use the Publish Report page.
To run a pre-built report:
In the Applications window, 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.
26.2.1.14.1.2 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:
- In the Applications window, expand the application and project.
- Right-click an offline database and choose New to display the New Gallery.
- From the New Gallery, expand Database Tier, and select Offline Database Objects.
- Select Reports to launch the Create Report wizard.
- 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.
- Change the offline database the report is to run on in the Offline Database page.
- 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.
- If you want to use parameters with the report, enter them in the Report Parameters page.
- Click Finish. The new report is listed in the Applications window under the offline database node.
To run a report:
In the Applications window, 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.
26.2.1.14.1.2.1 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:
<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.
26.2.1.14.1.2.2 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:
- In the Applications window, expand the application, project and offline database.
- Right-click the report and choose Properties to open the Edit Report dialog, where you can examine and change the properties.
26.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 UML-Offline Database Transformation
26.2.1.16 Working with Offline Database Objects in Source Control Systems
JDeveloper provides a number of features for developing in teams, including several version control software systems. These are described in Versioning Applications with Source Control.
Offline database 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 Database Objects wizard. For more information, see How to Generate Offline Database Objects to the Database .
26.2.2 Working with Database Objects
You can create database objects in a database connection in the Databases window.
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 Reference.
To create a database object in the Databases window:
If necessary, choose Window > Database > Databases window.
Expand IDE Connections or application, and expand the database connection.
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.
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:
If necessary, choose Window > Database > Databases window.
Expand IDE Connections or application, and expand the database connection, and navigate to the node and database object you want to edit.
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:
- If necessary, choose Window > Database > Databases window.
- Expand IDE Connections or application, and expand the database connection, and navigate to the node and database object you want to drop.
- Right-click and choose Drop.
26.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 window. For more information, see Using the Database Reports Window.
26.2.4 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 reverse engineer tables from a database, date and time values are validated according to the rules above. If the validation prevents you from reverse engineering a table from Oracle Database, you can turn it off.
To turn off date and time validation:
Choose Tools > Preferences > Database.
Uncheck Validate date and time default values.
26.3 Creating Scripts from Offline and Database Objects
You can generate database objects and offline database definitions to SQL scripts, Oracle MetaBase (OMB) files which can be imported into Oracle Warehouse Builder, or SXML files.
26.3.1 How to Create SQL Scripts
You can create SQL scripts from offline database definitions or from database objects.
The script is generated with the default name script1.sql. It is opened in the SQL Worksheet, and listed in the Applications window under the Resources node for the current project.
To create a SQL script from the Databases window or Applications Window:
Choose Window > Database > Databases window, expand the database connection and schema, and right-click the database object you want to create the script from.
or
Choose Window > Applications, navigate to the offline database definition you want to create the script from, and right-click the database object you want to create the script from.
Choose Generate to > SQL script.
The Generate SQL from Database Objects wizard opens where you specify the details of how to create the script. For more information at any time, press F1 or click Help in the dialog.
When you click Finish, the script is created and opened in the source editor.
To create a SQL script from the New Gallery:
26.3.2 How to Create OMB Scripts from Tables
You can create files formatted as Oracle MetaBase (OMB) scripts for Oracle Warehouse Builder from offline tables in the Applications window.
The file is generated with the default name omb_scriptn.tcl. It is opened in the source editor, and listed in the Applications window under the Resources node for the current project.
To create a OMB script from the Applications window:
Choose Window > Applications, navigate to the offline table or tables you want to create the file from.
Right-click and choose Generate to > OMB script.
The file is created and opened in the source editor.
To create a OMB script from the New Gallery:
26.3.3 How to Create SXML Scripts
You can create SXML files from offline tables in the Applications window.
A script is generated for each offline database object with the name object-name_object-type.xml. The scripts are opened in the XML Source Editor, and listed in the Applications window under the offline database node.
To create SXML files from the Applications window:
Choose Window > Applications, navigate to the offline table or tables you want to create the script from.
Right-click and choose Generate to > SXML.
The files are created and opened in the source editor.
To create SXML files from the New Gallery: