| Oracle® Fusion Applications Developer's Guide 11g Release 5 (11.1.5) Part Number E15524-10 |
|
|
PDF · Mobi · ePub |
| Oracle® Fusion Applications Developer's Guide 11g Release 5 (11.1.5) Part Number E15524-10 |
|
|
PDF · Mobi · ePub |
This chapter discusses database modeling and database schema deployment in Oracle Fusion Middleware.
When designing an application to interact with the database, you will need to understand the database schema and be able to modify the schema as needed. This chapter contains information regarding database modeling and database schema deployment in Oracle Fusion Middleware. Developers should not use SQL DDL scripts for deployment and source control of database objects, because they tend to be error-prone and do not serve as a single accurate source. Instead, developers should use the JDeveloper offline database schema object files in SXML persistence mode.
Note:
Prior to SXML migration, these were referred to as XDF (extension) files.
This chapter includes the following sections:
The Oracle Fusion Schema Deployment framework includes JDeveloper plugins that handle applications-specific metadata, data modeling standards for applications database modeling, and deployment of database schema objects to a target application database. The database schema deployment component can be invoked standalone outside of JDeveloper, such as from the command line, build scripts, or a patching tool.
Oracle uses source-controlled schema metadata files produced from JDeveloper. The Offline Database is a way to persist database object definitions in a JDeveloper project using SXML files, rather than accessing the database directly. It provides an abstract layer that can be used to access a store of database object definitions. Therefore, it is possible to create, edit, delete and manipulate aspects of a database schema offline and access database objects in a database through JDeveloper's connections.
All schema modeling can be done through JDeveloper. The XDF extension provides developers with a set of tools to do the data physical modeling, such as create, edit, deploy, and import the schema objects used in applications. The extension also provides Application Data Modeling Standard validation, modification, and template object plugins in JDeveloper to help users to follow the Data Model standards.
Developers will use XDF extensions for their database modeling development.
Information covered here includes:
The purpose of the offline database and how to create the database objects in the offline database using JDeveloper.
The functions provided by the XDF extension how to use the XDF deployment and import tools in JDeveloper.
The definitions of all User Defined Properties (UDP) that are provided by the XDF extension user property library.
The UDPs developers should and can define for their schema objects in JDeveloper.
JDeveloper provides the tools you need to create and edit database objects, such as tables and constraints, outside the context of a database, using the offline Database model. You can create new tables and views, and generate the information to a database, or you can import database objects from a database schema, make the changes you want, and generate the changes back to the same database schema, to a new database schema, or to a file that you can run against a database at a later date.
Follow these directions to create an offline database.
To create an offline database:
In the Application navigator within JDeveloper, locate the project you want to work in.
Select File > New to display the New Gallery.
From the New Gallery, select Database Tier > Offline Database Objects > Offline Database, as shown in Figure 56-1.
In the Create Offline Database dialog, enter a name for the offline database, as shown in Figure 56-2. For more information at any time, press F1 or click Help from within the Create Offline Database dialog.
The following types of objects are modeled using offline database objects.
Table
Trigger
View
Materialized View
Materialized View Log
Sequence
Synonym
JDeveloper offline database objects do not support these objects. However, SXML persistence files for these object types can be imported using the applxdf extension.
Queue
Queue tables
Policy
See Section 56.2.9.1, "Deploying in SXML Persistence Format."
A Framework plug-in on the JDeveloper database object editor provides warnings and errors to enforce Data Modeling standards and XDF deployment requirements.
Additional Validations for Schema Object Deployment
To better service the Schema Deployment on Application Data Model, these validations have been added on some User Defined Property (UDP) and other object properties in the plugin.
Table Owner (UDP)
For a table object, if the User Property Table Owner is not defined, an error will be displayed.
Short Name (UDP)
All schema objects:
If the length of the object name is greater than the length standard and if the short name is null or empty, display a warning.
If the short name is not null or empty, check if the length of the short name is greater than the length standard. If it is, display a warning message.
If the length of the object name is not greater than the length standard and if the short name is null or empty, automatically set the short name to be the same as the object name.
If the short name is not null or empty, check if the length of the short name is greater than the length standard. If it is, display a warning message.
The name length standard for a Table is 24; for all others, it is 27.
Adxml (UDP)
The UDP adxml is set automatically for these schema object types:
Table.TYPE View.TYPE Synonym.TYPE Sequence.TYPE MaterializedViewLog.TYPE MaterializedView.TYPE Trigger.TYPE
The content of the UDP adxml will be determined by the current value of the UDP adxml and the UDP useExistingAdxml.
AdxmlFK (UDP)
For table type only, automatically set the UDP adxmlFk according to the values of the UDP adxmlFk and useExistingAdxml.
AdxmlDeferredIndexes (UDP)
For table and MaterializedView.type only, automatically set the UDP adxmlDeferredIndexes according to the current value of this UDP and the value of UDP useExistingAdxml.
Active Constraint or Index's Columns Checking
Check the status of a column to which an active constraint or index refers. If its value is obsolete, an error message be displayed and block the work flow.
Index for Unique Constraint
Check if an index of unique constraint exists. If not, add one automatically.
Constraint Deployment Violation Checking
If a constraint is defined as disabled, but its UDP isLogical is set to N, a warning message will be displayed, because this case will cause a deployment error.
Dependencies/Risk
This feature uses JDeveloper's Offline database APIs and therefore has a dependency on all the offline database JAR files. The risk for this feature is some enforcement of standards may fire wrongly due to potential bugs preventing developers from modeling their objects as needed.
JDeveloper provides a large number of User Defined Properties (UDP). Their mapping with the tables in the dictionary are detailed in:
Table 56-1 shows the User Defined Properties that are defined for the tables.
Table 56-1 User Defined Properties for Tables
| UDP | Display Name | Values in JDeveloper | Definition in FND_TABLES |
|---|---|---|---|
|
This property indicates whether flashback of the table is allowed. This UDP is mandatory. |
Is Flashback Allowed |
Y: Flashback of the table is allowed. That is, you can use Flashback Query to examine the state of a table at a previous time, or use the FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. N (default): Flashback of the table is not allowed. |
FLASHBACK_ALLOWED |
|
The value of this UDP indicates whether the deployment program will create an editioning view for this table or not. This UDP is required. |
Editioning View |
Y (default): The deployment program will create an editioning view for this table. N: The deployment program will not create an editioning view for this table. |
LOGICAL VARCHAR2(1) Not Null |
|
|
Run Deployment Twice |
Y: Specifies that the table needs to be deployed twice and the appropriate patching metadata will be stamped in the file. This is typically used when a product team adds or modifies a column as a not-null column to a existing table, and does not want to use a RBMS default value. The column will be populated with an upgrade script having a more complex logic. The column needs to exist in the target database before the upgrade script is run. Also, the upgrade script cannot enforce the not-null constraint since it is against the standards to have DDL in scripts. Setting this UDP to Y will accommodate this.N (default). |
N/A |
|
The short name of the table is used by the Zero Downtime programs to uniquely identify the table. The maximum length of this UDP is 24 characters. |
Table Short Name |
N/A The value of this UDP is defaulted to the table name when the length of the table name is less than 24 characters. When the length of the table name is greater than 24 characters, this UDP is required. |
SHORT_NAME VARCHAR2(30) Null |
|
This UDP stores the Short Name of the application that the table belongs to. This UDP is required. |
Table Owner |
N/A |
APPLICATION_SHORT_NAME VARCHAR2(10) |
|
This UDP stores the Tablespace Classification for this table. This value is used to derive the tablespace for the table but it is not equivalent to the tablespace. This UDP is mandatory. |
Tablespace Classification |
TRANSACTION TABLES (default) REFERENCE INTERFACE SUMMARY NOLOGGING TRANSACTION_INDEXES ARCHIVE TOOLS MEDIA |
N/A |
|
The value of this UDP indicates the language data model for the table. This UDP is required. |
MLS Support Model |
Not MLS (default): The table is not an MLS table. Fully Synched: For Standard MLS (pair of _B and _TL tables) or Single MLS (single _TL) tables. A record will exist in the _TL table for each licensed language in the instance. The _TL table must have LANGUAGE and SOURCE_LANG columns. Partially Synched: For Partially Synchronized MLS tables. These tables have a LANGUAGE column but do not have a SOURCE_LANG column. A record may or may not exist in the table for the licensed languages in the instance. Single Language: For Single Language tables. These tables do not have either a LANGUAGE or a SOURCE_LANG column. The language of the data in the translatable columns in the table is considered to be the language classified as the default or base language of the instance. |
MLS_SUPPORT_MODEL VARCHAR2(30) |
|
The value of this UDP indicates the status of the table. This UDP is required. |
Status |
Active (default): The table is active. Obsolete: The table is obsolete and can be deleted from the database. |
STATUS VARCHAR2(30) |
|
This UDP stores the name of the base table that is extended by this table. |
Extension of Table |
N/A |
EXTENSION_OF_TABLE VARCHAR2(30) |
|
The value of this UDP indicates the deployment mode of the table for the Oracle Fusion Disconnected Mobile Framework. This UDP is required. |
Deploy To |
Server DB Only (default): The table is deployed on the server database but not on the mobile database. All: The table is deployed both on the server database and on the mobile database. Mobile DB Only: The table is deployed on the mobile database but not on the server database. |
DEPLOY_TO VARCHAR2(30) |
|
The value of this UDP indicates how the Oracle Fusion Disconnected Mobile Framework should resolve the conflicts about duplicate rows. This UDP is required. |
Conflict Resolution |
Duplicate (default): A new duplicate record is added and the conflict will be handled during the next synchronization. This value should be used for non-intersection tables. Merge: The records are merged. This value should be used for intersection tables. |
CONFLICT_RESOLUTION VARCHAR2(30) |
|
The value of this UDP indicates whether the table is accessed by external products. |
Shared Object |
Y: The table can be accessed directly by external products, other than the owning product. N (default): The table cannot be accessed directly by external products. |
SHARED_OBJECT VARCHAR2(30) |
|
The value of this UDP is patch metadata used by the patching tool. |
ADXML |
N/A |
N/A |
|
The value of this UDP is patch metadata used by the foreign key portion of the patching tool. |
ADXML for Foreign Keys |
N/A |
N/A |
|
The value of this UDP is patch metadata used by the deferred indexes portion of the patching tool. |
ADXML for Deferred Indexes |
N/A |
N/A |
|
This UDP is mandatory. |
Use Existing ADXML |
Y: Generate ADXML comment using the existing ADXML value from the ADXML UDP N (default): Regenerate the ADXML comment and update the ADXML UDP. |
N/A |
|
This property indicates whether the select on the table is allowed. This is mandatory. |
Is Select Allowed |
Y (default)N |
SELECT_ALLOWED VARCHAR2(1) Not Null |
|
This property indicates whether update on the table is allowed. This property is mandatory. |
Is Update Allowed |
Y (default)N |
UPDATE_ALLOWED VARCHAR2(1) Not Null |
|
This property indicates whether the insert on the table is allowed. This property is mandatory. |
Is Insert Allowed |
Y N (default) |
INSERT_ALLOWED VARCHAR2(1) Not Null |
|
This property indicates whether delete on the table is allowed. This property is mandatory. |
Is Delete Allowed |
Y (default)N |
DELETE_ALLOWED VARCHAR2(1) Not Null |
|
This property indicates whether truncate on the table is allowed. This property is mandatory. |
Is Truncate Allowed |
Y N (default) |
TRUNCATE_ALLOWED VARCHAR2(1) Not Null |
|
Indicates whether partitions can be maintained on the table. This property is mandatory. |
Maintain Partition |
Y N (default) |
MAINTAIN_PARTITION VARCHAR2(1) Not Null |
|
Indicates whether it is possible to exchange partitions on the table. This property is mandatory. |
Exchange Partition |
Y N (default) |
EXCHANGE_PARTITION VARCHAR2(1) Not Null |
|
This property indicates whether it is possible to maintain indexes on the table. This property is mandatory. |
Maintain Index |
Y N (default) |
MAINTAIN_INDEX VARCHAR2(1) Not Null |
Table 56-2 shows the User Defined Properties that are defined for columns.
Table 56-2 User Defined Properties for Columns
| UDP | Display Name | Values in JDeveloper | Definition in FND_COLUMNS |
|---|---|---|---|
|
The short name of the column is used by the Zero Downtime programs to uniquely identify the column within the table. The value of this UDP is defaulted to the column name when the length of the column name is less than 27 characters. When the length of the column name is greater than 27 characters, this UDP is required. |
Column Short Name |
N/A |
SHORT_NAME VARCHAR2(27) Null |
|
The value of this UDP indicates whether the column is translatable or not. This UDP is required. |
Translate |
Y: The column is translatable. N (default): The column is not translatable. |
TRANSLATE_FLAG VARCHAR2(1) Not Null |
|
The value of this UDP indicates the status of the column. This UDP is required. |
Status |
Active (default): The column is active. Obsolete: The column is obsolete and can be deleted from the database. |
STATUS VARCHAR2(30) |
|
|
Custom Default Value |
N/A |
N/A |
|
The value of this UDP indicates the name of the column that stores the data that should be copied to this column as per the Oracle Fusion Disconnected Mobile Framework. |
Denormalization Path |
N/A |
N/A |
|
The value of this UDP indicates how this column will be handled during the synchronization between the server and the client database as per the Oracle Fusion Disconnected Mobile Framework. |
Routing Mode |
Normal (default): The contents of this column must be routed to the destination database. Do Not Route: The contents of this column must not be routed to the destination database. |
ROUTING_MODE VARCHAR2(30) |
|
The value of this UDP indicates if the column is a candidate for histogram. This UDP is required. |
Histogram |
Y (default): This column is a candidate for histogram. N: This column is not a candidate for histogram. |
N/A |
|
The value of this UDP indicates the number of buckets to be used when the column is defined as a candidate for histograms. |
Histogram Size |
N/A |
|
|
The value of this UDP indicates the name of the version column used by the Oracle Fusion Disconnected Mobile Framework during synchronization of LOB columns. |
Disconnected Mobile Version Column Name |
N/A |
VERSION_COLUMN VARCHAR2(30 CHAR) |
Table 56-3 shows the User Defined Properties that are defined for the indexes.
Table 56-3 User Defined Properties for Indexes
| UDP | Display Name | Values in JDeveloper | Definition in FND_INDEXES |
|---|---|---|---|
|
The short name of the index is used by the Zero Down Time patching programs to uniquely identify the index. The value of this UDP is defaulted to the index name when the length of the index name is less than 28 characters. When the length of the index name is greater than 28 characters, the developer must enter a value that uniquely identifies the index. |
Index Short Name |
N/A |
SHORT_NAME VARCHAR2(30) Null |
|
The value of this UDP indicates whether the creation of the index will be deferred during deployment. This UDP is required. |
Index Deferred (Y/N) |
N (default): The creation of the index will not be deferred. Y: The creation of the index will be deferred. |
N/A |
|
The value of this UDP indicates the status of the index. This UDP is required. |
Status |
Active (default): The index is active. Obsolete: The index is obsolete and can be deleted from the database. |
STATUS VARCHAR2(30) |
|
The value of this UDP indicates the deployment mode of the index for the Oracle Fusion Disconnected Mobile Framework. This UDP is required. |
Deploy To |
Server DB Only (default): The index is deployed on the server database but not on the mobile database. All: The index is deployed both on the server database and on the mobile database. Mobile DB Only: The index is deployed on the mobile database but not on the server database. |
DEPLOY_TO VARCHAR2(30) |
Table 56-4 shows the User Defined Properties that are defined for the constraints.
Table 56-4 User Defined Properties for Constraints
| UDP | Display Name | Values in JDeveloper | Definition in FND_PRIMARY_KEYS or FND_FOREIGN_KEYS |
|---|---|---|---|
|
|
Logical Constraint |
Y (default)N |
LOGICAL VARCHAR2(1) |
|
The short name of the constraint is used by the Zero Down Time patching programs to uniquely identify the constraint. The value of this UDP is defaulted to the constraint name when the length of the constraint name is less than 28 characters. When the length of the constraint name is greater than 28 characters, the developer must enter a value that uniquely identifies the constraint. |
Constraint Short Name |
SHORT_NAME VARCHAR(30) |
|
|
The value of this UDP indicates whether the creation of the constraint will be deferred during deployment. This UDP is required. |
Defer Constraint |
N (default): The creation of the constraint will not be deferred. Y: The creation of the constraint will be deferred. |
N/A |
|
The value of this UDP indicates the status of the constraint. This UDP is required. |
Status |
Active (default): The constraint is active. Obsolete: The constraint is obsolete and can be deleted from the database. |
N/A |
Table 56-5 shows the User Defined Properties that are defined for the views.
Table 56-5 User Defined Properties for Views
| UDP | Display Name | Values in JDeveloper | Definition in FND_VIEWS |
|---|---|---|---|
|
The value of this UDP is patch metadata used by the patching tool. |
ADXML |
N/A |
N/A |
|
This property indicates whether flashback of the view is allowed. This UDP is mandatory. |
Is Flashback Allowed |
Y: Flashback of the view is allowed. That is, you can use Flashback Query to examine the state of a view at a previous time. N (default): Flashback of the view is not allowed. |
FLASHBACK_ALLOWED |
|
useExistingAdxml |
Use Existing ADXML |
Y: Generate ADXML comment using existing ADXML value from ADXML UDP. N (default): Regenerate ADXML comment and update ADXML UDP. |
N/A |
|
The value of this UDP indicates the status of the view. This UDP is required. |
Status |
Active (default): The view is active. Obsolete: The view is obsolete and can be deleted from the database. |
STATUS VARCHAR2(30) |
|
This property indicates whether the select on the table is allowed. This is mandatory. |
Is Select Allowed |
Y (default)N |
SELECT_ALLOWED VARCHAR2(1) Not Null |
|
This property indicates whether update on the table is allowed. This property is mandatory. |
Is Update Allowed |
Y (default)N |
UPDATE_ALLOWED VARCHAR2(1) Not Null |
|
This property indicates whether the insert on the table is allowed. This property is mandatory. |
Is Insert Allowed |
Y N (default) |
INSERT_ALLOWED VARCHAR2(1) Not Null |
|
This property indicates whether delete on the table is allowed. This property is mandatory. |
Is Delete Allowed |
Y (default)N |
DELETE_ALLOWED VARCHAR2(1) Not Null |
Table 56-6 shows the User Defined Properties that are defined for Sequence.
Table 56-6 User Defined Properties for Sequence
| UDP | Display Name | Values in JDeveloper | Definition in FND_SEQUENCES |
|---|---|---|---|
|
|
Sequence Owner |
N/A |
N/A |
|
The value of this UDP indicates the status of the sequence. This UDP is required. |
Status |
Active (default): The sequence is active. Obsolete: The sequence is obsolete and can be deleted from the database. |
STATUS VARCHAR2(30) Null |
|
The value of this UDP is patch metadata used by the patching tool. |
ADXML |
N/A |
N/A |
|
|
Use Existing ADXML |
Y: Generate ADXML comment using existing ADXML value from ADXML UDP. N (default): Regenerate ADXML comment and update ADXML UDP. |
N/A |
|
This property indicates whether select on the table is allowed. This UDP is mandatory. |
Is Select Allowed |
Y (default)N |
SELECT_ALLOWED VARCHAR2(1) Not Null |
|
This property indicates if the sequence can be reset to a specific value. This UDP is mandatory. |
Reset Sequence |
Y N (default) |
RESET_SEQUENCE VARCHAR2(1) Not Null |
Table 56-7 shows the User Defined Properties that are defined for the Materialized View.
Table 56-7 User Defined Properties for Materialized View
| UDP | Display Name | Values in JDeveloper | Definition in FND_MVIEWS |
|---|---|---|---|
|
Short Name of the application to which this materialized view belongs. |
Mview Owner |
N/A |
N/A |
|
Materialized view short name. Max Length is 24. |
Materialized View Short Name |
N/A |
SHORT_NAME VARCHAR2(30) |
|
The value of this UDP indicates the status of the materialized view. |
Status |
Active (default): The materialized view is active. Obsolete: The materialized view is obsolete and can be deleted from the database. |
STATUS VARCHAR2(30) |
|
The value of this UDP is patch metadata used by the patching tool. |
ADXML |
N/A |
|
|
|
Tablespace Classification |
TRANSACTION_TABLES REFERENCE INTERFACE SUMMARY (default) ARCHIVE TOOLS MEDIA |
N/A |
|
This UDP is mandatory. |
Use Existing ADXML |
Y: Generate ADXML comment using existing ADXML value from ADXML UDP. N (default): Regenerate ADXML comment and update ADXML UDP. |
N/A |
|
This property indicates whether select on the table is allowed. This UDP is mandatory. |
Is Select Allowed |
Y (default)N |
SELECT_ALLOWED VARCHAR2(1) Not Null |
|
The value of this UDP is patch metadata used by the deferred indexes portion of the patching tool. |
ADXML for Deferred Indexes |
N/A |
N/A |
Table 56-8 shows the User Defined Properties that are defined for the Materialized View Log.
Table 56-8 User Defined Properties for Materialized View Log
| UDP | Display Name | Values in JDeveloper |
|---|---|---|
|
The value of this UDP indicates the status of the materialized view log. This UDP is required. |
Status |
Active (default): The materialized view log is active. Obsolete: The materialized view log is obsolete and can be deleted from the database. |
|
Materialized view log owner. |
MV Log Owner |
N/A |
|
The value of this UDP is patch metadata used by the patching tool. |
ADXML |
|
|
This UDP is mandatory. |
Y N (default) |
Y: Generate ADXML comment using existing ADXML value from ADXML UDP. N (default): Regenerate ADXML comment and update ADXML UDP. |
Table 56-9 shows the User Defined Properties that are defined for Trigger.
Table 56-9 User Defined Properties for Trigger
| UDP | Display Name | Values in JDeveloper |
|---|---|---|
|
The value of this UDP indicates the status of the trigger. This UDP is required. |
Status |
Active (default): The trigger is active. Obsolete: The trigger is obsolete and can be deleted from the database. |
|
|
Trigger Owner |
N/A |
|
The value of this UDP is patch metadata used by the patching tool. |
ADXML |
|
|
This UDP is mandatory. |
Use Existing ADXML |
Y: Generate ADXML comment using existing ADXML value from ADXML UDP. N (default): Regenerate ADXML comment and update ADXML UDP. |
To create new offline database objects from within JDeveloper, in the Application Navigator:
Right-click the offline Database or schema.
Select New Database Object.
Select any Offline database object definition that you wish to create.
You also can create an offline database object definition by importing an existing definition from an online database schema.
To edit an offline database object:
In the Application Navigator, expand the workspace, project, and schema.
Right-click the offline database object that you wish to edit and choose Properties. Or double-click the offline database object.
The Edit offline database object dialog opens. For more information at any time, press F1 or click Help from within the Edit dialog.
In the Edit dialog, select an information category on the left and change the values in the panel on the right. Any items that are grayed out cannot be selected or changed.
Database objects from a database schema can be imported to an offline database project in JDeveloper. JDeveloper extensions will also handle the additional Oracle Fusion metadata, if available in the target database. The additional metadata will be copied to an offline database object as user-defined properties. For objects not supported by JDeveloper, such as Policy and Advanced queue tables, import of object definitions from the database will be provided. Implementation of unsupported object import process APIs depends on functionality provided by the Metadata team (dbms_metadata).
Using the Import Offline Database Object Wizard
Object definitions can be generated to the Offline Database by right-clicking an Offline Database Source and selecting the Reverse Engineer Fusion Applications Objects option, extended to invoke the relevant import API, as shown in Figure 56-3.
The target database connection name can be selected from the list of all defined database connections, or a new connection can be created in the Specify Source dialog, shown in Figure 56-4.
Select the Project and Offline database to which the objects from the target database need to be imported, as shown in Figure 56-5.
Filters can be applied to select the objects that are displayed as available for import. When there are a large number of objects in the schema, you should apply filters.
In the Object Picker, shown in Figure 56-6, you can:
Enter characters in the Name Filter to filter the list of available objects by name. The Name Filter is case sensitive.
When there are a large number of objects, you can turn off Auto-Query and click Query once you have entered the filter you want to use.
Select the object types you want to view.
Click Next to display the summary information.
Click Finish to import the selected objects to the specified offline database.
Once an offline database object is created, it can be deployed to a target database using the deployment extension provided in JDeveloper.
As part of the Oracle Middleware Extensions for Applications (Applications Core) labels, the applxdf extensions for the JDeveloper offline database include a deployment program that operates on JDeveloper offline database objects in SXML format. It checks for and compares the object definitions in SXML format with the object definitions in the target database, and then executes the necessary create/alter DDL to deploy the objects. This deployment program is available in two forms:
Deployment wizard extension that can be invoked from within JDeveloper.
Standalone deployment program that can be invoked from the command line.
To start the deployment wizard in JDeveloper, you need to choose APPS: Deploy DB Object from the context menu on the offline object definition in the Application Navigator.
This can be used to deploy an offline database to a target online database. These options are available for deployment:
Deploying a single database object file. Only one item is selected, as shown in Figure 56-7.
Deploying multiple database object files (Bulk Deployment). Several items are selected, as shown in Figure 56-8.
Deploying offline schema object (and thereby deploying the entire designed data model), as shown in Figure 56-9.
The Generate Fusion Applications Objects wizard will prompt for the following information:
Database Connection: The target database connection name can be selected from the list of all defined database connections, or a new connection can be created, as shown in Figure 56-10.
Deployment Parameters. Figure 56-11 shows how the dialog appears for single database deployment parameters.
Figure 56-12 shows how the Deployment Parameters dialog appears for multiple database deployment parameters.
Owner User: Oracle schema name in which the object exists or should be created.
Log File Path: Specify a logfile name if it has to be written to a log file. By default, the log will be displayed in the JDeveloper log window.
For Single Database Object deployment, this is optional. For bulk Database Object and schema deployment, it is mandatory to provide a directory for saving log files.
Log File Format: The format of the log file. Permitted values are text (the default) or xml.
Debug Level: The Debug level controls the level of detail to be captured in the log. Debug Level=3 will show the most information. Permitted values are 0 (the default), 1, 2 or 3.
Db Object Mode: A single database object can be deployed independently in table and in tablefk mode.
In case of bulk and schema deployment, the database objects first will be deployed in table mode and then tablefk mode, by default.
This is standard for bulk and schema deployment; therefore, the Db Object Mode is not displayed on the wizard.
Stand Alone: If this option is selected, the XDF comparison utility will execute in a standalone mode. This mode does not have any applications dependencies and it creates database objects without applications standards for physical attributes such as TABLESPACE/STORAGE; the database defaults are used.
Change Database: This option indicates whether the deployment should just report or execute the necessary Alter DDLs, based on comparison of the offline Database object definition against target database. If unchecked, the deployment will report on the differences but will not actually apply the changes to the database.
Force Mode: If this option is selected, any additional column, index or constraints that are present in a target database, but not in the current object file, will be dropped.
Use this command and parameters shown in Example 56-1 to deploy a database object from the command line.
Example 56-1 Sample Database Object Deployment Using the CLI
java oracle.apps.fnd.applxdf.comp.XdfSchemaDeploy <owner_un={schemaId}> <apps_un={appId}> <jdbc_protocol={jdbc driver type}> <jdbc_db_addr={jdbc tns info}> <xdf_file_name={xdf file name}> <xdf_mode={xdf mode}> [xdf_xsl_dir={xsl file directory}] [standalone={y|n}] [changedb={y|n}] [logfileformat={text|xml}] [logfile={log file path and name}] [debuglevel={0|1|2|3}] [from_jdev={y|n}]*
Mandatory Arguments
owner_un: Oracle schema name in which the object exists or should be created.
apps_un: Oracle schema name of the current APPS schema.
Note that in the consolidated fusion schema model, owner_un will be the same as apps_un. These parameters are maintained for cases where they could be different.
jdbc_protocol: The JDBC protocol (thin or oci8).
jdbc_db_addr: JDBC tns information, either formatted as a Net8 connect string enclosed in double quotes, or as hostname:port:oracle_sid.
xdf_mode: The object type information - table, qtable, mview, mviewlog, sequence, type, trigger, view, policy, bootstrap.
xdf_file_name: The XDF file name, which contains the object definition. It is not mandatory if the xdf_mode is bootstrap.
Password Arguments
The command line deployment tool will prompt to get the database password from the user in an interactive mode. The password cannot be a parameter, because it is against Security guidelines. If you have a script in which you are invoking schema deployment, you can pipe the password in the script, such as:
$JDEV_JAVA_HOME/bin/java oracle.apps.fnd.applxdf.comp.XdfSchemaDeploy owner_un=$FUSION_SCH apps_un=$FUSION_SCH jdbc_protocol=thin jdbc_db_addr=$JDBC_ADDR changedb=y logfileformat=text xdf_file_name=$xdfFile xdf_mode=$xdf_mode logfile=$logfile <<! $FUSION_PASS
Optional Parameters
xdf_xsl_dir: The XSL directory, which contains all the XSL files required for XSLT transformation. This parameter is optional and is automatically determined in most cases if the JAR file format of deploying Java class files is being used. This parameter is maintained for flexibility, in case the format for deploying Java files becomes similar to what existed in previous versions.
standalone: This option is used to execute the XDF comparison utility in a standalone mode. Permitted values are y, Y, n or N. The default value is n. Standalone=y does not have any applications dependency. This mode creates database objects without applications standards for physical attributes such as TABLESPACE/STORAGE and uses the database defaults. It also does not update applications metadata.
changeDb: The default is "y." If changedb is specified as "n," the SQL statements generated by the XDF comparison utility are not executed but are displayed on the standard output or a log file.
logfileformat: The format of the log file. Permitted values are text or xml. If logfileformat is not set, the default is text.
logfile: The output of the comparison utility is written to standard out. Specify a logfile name if it has to be written to a log file. If logfile is not set, the outputs will be displayed on the screen.
debuglevel: Debug levels determine how much information is to be shown in the log. Debuglevel=3 will show the most information. Permitted values are 0, 1, 2 or 3. The default value is 0.
from_jdev: This parameter is set to "y" when the XDF comparison utility is called from JDeveloper. The default value is "n."
force_mode: The force deployment mode introduces an additional input parameter that when specified will drop any additional column, index or constraints that are present in a target database. The applications metadata stored for the object is also updated to be in sync with the new definition.
index_category: Values are small, large, and both. If the table is partitioned, the index is always created. If the table is not partitioned, there is no index creation if one of these conditions is true:
index_category=small and unused dbms block size greater than parallel_index_threshold
index_category=large and unused dbms block size less than parallel_index_threshold
parallel_index_threshold: Parallel index threshold, default is 0.
no_error: This option is used when you add a not-null column to an existing table with data, or change a null column to a not-null column. XdfSchemaDeploy results in FAILURE without this option. XdfSchemaDeploy results in WARNING if you have no_error=y. Default value is "n."
idxnolog: Pass idxnolog=y to add a NOLOGGING clause in the Index creation to improve the performance of creation. The default value is "n."
The XdfSchemaDeploy tool requires JDK 1.6, the standard Oracle JDBC driver, the XML parser, and the applxdf JAR file.
Set the CLASSPATH environment variable to contain these JAR files:
ojdl.jar (Only DROP8 Build 3)
ojdl2.jar (Only DROP8 Build 3)
xmlparserv2.jar
ojdbc6.jar
orai18n.jar
oracle.apps.fnd.applxdf.jar
Example 56-2 shows the set of commands to set the CLASSPATH.
Example 56-2 Example of Setting CLASSPATH
setenv CLASSPATH $ADE_VIEW_ROOT/fmwtools/BUILD_HOME/oracle_common/modules/oracle.nlsrtl_11.1.0/orai18n.jar: $ADE_VIEW_ROOT/fmwtools/BUILD_HOME/oracle_common/modules/oracle.xdk_11.1.0/xmlparserv2.jar: $ADE_VIEW_ROOT/fmwtools/BUILD_HOME/wlserver_10.3/server/ext/jdbc/oracle/11g/ojdbc6.jar: $ADE_VIEW_ROOT/fmwtools/BUILD_HOME/oracle_common/modules/oracle.odl_11.1.1/ojdl.jar: $ADE_VIEW_ROOT/fmwtools/BUILD_HOME/oracle_common/modules/oracle.odl_11.1.1/ojdl2.jar: $MW_HOME/jdeveloper/jdev/extensions/oracle.apps.fnd.applxdf.jar
The JDeveloper installation directory could potentially change with newer versions of JDeveloper being available. Check the JDeveloper installation directory to make sure that it exists. You could also use the XML parser and JDBC driver that comes with the database. Note that so far XDF has been tested with the same JAR files with which it has been compiled. It should not be a problem setting the CLASSPATH with a higher version of these JAR files and testing them. If you encounter any issues, try using 11g JDBC and xmlparsers.
Bootstrap mode for XDF Schema deployment is available using the parameter xdf_mode=bootstrap. XDF currently depends on several database components, such as pl/sql and tables, for it to work completely in all modes. The bootstrap mode can be used to make sure that the XDF database dependencies are set up correctly and to avoid any manual steps to get XDF working on a particular database.
In bootstrap mode, the mandatory parameter xdf_file_name becomes optional and only the remaining mandatory parameters are applicable.
To run XDF in bootstrap mode, use the command shown in Example 56-3.
Examining these frequently-asked questions about deployment will help you prevent and fix problems.
To add a not-null column to an existing table with data, or change a null column to a not-null column:
There are two options to add a not-null column to an existing table with data, or change a null column to a not-null column.
Option 1
Adding a not null column to an existing table with data
Product teams can specify an RDBMS default value for the column which will be used by the databases to successfully alter the table to add the not-null column.
Modifying a null column to not-null in a table with data
Product teams can specify an RDBMS default value for the column. This default value will be used by schema deployment utility to update the existing null rows with that value before changing the column to not null.
Option 2
If a product team does not want to use a RDBMS default value, it can add or modify a column as a not-null column to an existing table by using a script having a more complex logic. The column needs to exist in the target database before the script is run. The script cannot enforce the not-null constraint because it is against the standards to have DDL in scripts.
To use a script to populate the column, the UDP named runTwice must be set to Yes. This UDP will be used by XDF to ensure that the required patch metadata is present in XDF to run it twice in a patch. In the first run, the script will not error out if it is not able to enforce the not-null constraint, but it will error out in the second run if it still is not able to enforce the not-null constraint.
If the user does not set this UDP, the default behavior of the deployment utility is to error out if it is not able to enforce the not-null constraint while adding or modifying the column.
To remove a table, column or view:
See Section 56.2.9.5.3, "How to Use fnd_cleanup_pkg and fnd_drop_obsolete_objects."
To rename a table, column or view:
Deployment does not support renaming a table, column or view. The workaround that can be used is to make the object obsolete and introduce a new renamed object. The development team must separately handle data migration and update information in the Automatic Diagnostic Repository (ADR), if required.
To implement a non-additive change to the data type of a column, such as varchar2 to number:
Developers can create a script that runs before deployment of the object to rename or drop the column, based on whether or not data needs to be preserved or migrated. Once the deployment successfully adds the column with the correct data type, another script may be needed to make sure that data is migrated and that the renamed column is dropped. This is applicable only if the initial script renames the column.
To add or change the unique constraints or indexes on a populated table:
If the populated table does not meet the criteria for creating unique constraint or unique index, to remove the invalid data create a script to clean the table before deploying a unique index or constraint.
To maintain efficiency, database objects should be cleaned of no-longer-used data. As part of that process, it is important to keep the FND data dictionary synchronized with existing objects (it can be table, sequence or view) in the database. The XDF team provides packages to make this process easier.
Use this information to correctly make a database object obsolete. It is applicable after the release of the initial version of the product to customers.
Modeling database schema for new releases or upgrades to new releases may involve making certain database objects or certain attributes of the database object, such as Columns, obsolete. Doing this may make a significant effect to existing customizations or extensions currently implemented on the system. Making obsolete database objects that contain data, such as Tables, requires particularly close analysis for potential effects. Development teams should take the necessary steps to review and understand the implications of such updates in these areas.
Making obsolete certain database objects or certain attributes of a database object may require those objects to be dropped as part of clean up. Considering any existing customization or extensions to such objects, the act of making obsolete and dropping the object should be kept separate. Dropping the obsolete database objects or columns should be an optional step that is invoked at the demand of customers. The Patching (AD) utilities will provide such an option as a post-patching step.
Follow these steps to make obsolete a database object or attribute.
Set Status User Defined Property for the specific database objects or attributes to Obsolete. This can be done using User Defined Properties for Applications specific metadata that is part of the offline database model.
Status User Defined Property will be captured in the Applications/XDF data dictionary as part of deploying XDF to the database.
These steps ensure that:
If the table does not already exist in the database, the Applications schema deployment utilities (XDF) will create the table without columns marked as obsolete.
If the column does not already exist in the table, the Applications schema deployment utilities (XDF) will not add the column to the table.
If the column already exists in the table, the Applications schema deployment utilities (XDF) will remove any NOT NULL, PK/FK constraints on the column.
Any indexes that comprise only the obsolete columns could be dropped. In other cases, the development team owning the obsolete objects will be expected to update the definition of any affected indexes.
During the initial development of the product before release, product teams may not want to mark the object as obsolete and may prefer directly dropping the object; that is, removing the definition from the offline database file. To support this, the force_mode=y parameter can be passed to the schema deployment tool. The additional input parameter which, when specified, will drop any additional column, index or constraints that are present in a target database and not present in the offline object file definition. The XDF dictionary metadata stored for the object is also updated to be synchronized with the new definition. Note that this option, in certain cases, will not change the definition of the table to exactly match the definition in the file. For example, if the database does not allow some changes, such as changing of certain column datatype, or changing an unpartitioned table to a partitioned table, force mode will not override the database.
Force mode only handles the removal of column, index and constraints, and synchronizing the corresponding definition in the XDF dictionary. If the primary object, such as a table, sequence or view, is dropped, the fnd_cleanup_pkg needs to be used to synchronize the XDF dictionary.
Use fnd_cleanup_pkg and fnd_drop_obsolete_objects to clean a database.
Using fnd_cleanup_pkg
Procedure fndcleanup(name): Remove table, sequence, or view with name that is in fnd_tables, fnd_views or fnd_sequences tables, but not in the database. All the required XDF dictionary tables will be updated when fnd_tables, fnd_views, or fnd_sequences is updated.
name: Optional. This can be a table name, a view name or a sequence name. If a name is provided, the procedure will clean only the named object and related components. If it is not defined, the fndcleanup procedure removes all table, view, or sequences that are in fnd_tables, fnd_views, or fnd_sequence tables, but that do not exist in the database.
Procedure clean_fndcons(tbname): Remove the tbname table's Primary Key (PK), Unique Keys (UKs), and Foreign Keys (FKs) that are not in the database from the fnd constraint dictionary tables. The tbname parameter is optional. If it is not specified, then all tables' PK, UKs, and FKs that are not in the database will be removed from the fnd constraint tables.
Procedure TableFndCleanUp(tbname, deleteType, delname): Remove the specified deleteType with the specified delname on the specified tbname table from related fnd tables. The delname parameter is optional. If it is not specified, all table properties on that deleteType will be removed from the specified table. The deleteType includes column, index, pkuk, and fk.
Examples
Remove all table, view, or sequence information in fnd_tables, fnd_views, or fnd_sequences tables, but not in the database. All the required XDF dictionary tables will be updated when fnd_tables, fnd_views, or fnd_sequences is updated.
execute fnd_cleanup_pkg.fndcleanup
Remove table1 from the fnd_tables table if table1 is not in the database. All required XDF dictionary tables will be updated when fnd_tables is updated.
execute fnd_cleanup_pkg.fndcleanup('table1')
Remove view1 from the fnd_views table if view1 is not in the database.
execute fnd_cleanup_pkg.fndcleanup('view1')
Remove all table names LIKE HZ% in fnd_tables or fnd_views tables that do not exist in the database.
execute fnd_cleanup_pkg.fndcleanup('HZ%')
Remove table XF1's PK, UKs, and FKs that are not in the database, from the fnd constraint dictionary tables.
exec fnd_cleanup_pkg.clean_fndcons('XF1');
Remove a Foreign Key named XF2_T1_FK on table XF2 from the fnd constraint dictionary tables. XF2_T1_FK may or may not be in the database.
exec fnd_cleanup_pkg.tablefndcleanup('XF2', 'fk', 'XF2_T1_FK');
fnd_drop_obsolete_objects
Procedure drop_object(objectname): Drop obsolete objectname from the database. This procedure is used to delete obsolete views, tables and columns that are marked as Obsolete in the table.
Examples
Drop Table table1 from the database if it is marked as obsolete. If table1 is not obsolete, drop any columns in table1 that are obsolete.
execute fnd_drop_obsolete_objects.drop_object(table1)
Drop View view1 from the database if it is marked as obsolete.
execute fnd_drop_obsolete_objects.drop_object(view1)
Verify all tables and views with name like 'HZ_%" for dropping tables and views, or drop columns if the tables are not obsolete.
execute fnd_drop_obsolete_objects.drop_object('HZ_%')
Use this information when dropping an object in the database.
How do I make an object obsolete?
Select the object in JDeveloper.
Right-click and select Properties > User Properties.
Change the Status to Obsolete.
What happens when you deploy an obsolete object or an object that has obsolete columns or indexes?
Only the FND dictionary is updated. Objects in the database are not dropped.
How do I remove an object from the database and keep the FND data dictionary synchronized?
There are three ways this can be done. SQL scripts can be used to achieve the same outcome.
If the object is not a primary object and is a column, index or constraint that is being dropped:
Use JDeveloper to remove the definition of these secondary objects from the offline database file definition.
Use the force_mode optional parameter to deploy the object to the target database.
If the object is a primary object, such as a table, sequence or view:
Drop the object obj from sqlplus.
Execute this command from sqlplus:
execute fnd_cleanup_pkg.fndcleanup(' obj')
Change the object obj status UDP to Obsolete from JDeveloper. Deploy the object to the database either by command line (XdfSchemaDeploy) or use Generate Fusion Applications Objects from JDeveloper.
Execute this command from sqlplus:
execute fnd_drop_obsolete_objects.drop_object(' obj')
Execute this command from sqlplus:
execute fnd_cleanup_pkg.fndcleanup(' obj')
How do I clean up the FND data dictionary if many objects are no longer in the database?
Execute this command from sqlplus or use a SQL script.
execute fnd_cleanup_pkg.fndcleanup
How do I clean up the FND data dictionary if I had deleted columns and indexes from table, but did not cleanup from the FND data dictionary.
Execute this command from sqlplus or use a SQL script.
execute fnd_cleanup_pkg.fndcleanup
How do I remove obsolete columns/indexes in objects from FND data?
Execute this command from sqlplus.
execute fnd_cleanup_pkg.fndcleanup
How do I remove all table/view/sequence name LIKE HZ% in fnd_tables/fnd_views/fnd_sequences tables that do not exist in the database?
Execute this command from sqlplus.
execute fnd_cleanup_pkg.fndcleanup('HZ%')
How do I drop table/view/sequence name xyz in fnd_tables/fnd_views/fnd_sequences tables from the database if it is marked as obsolete?
Execute this command from sqlplus.
execute fnd_drop_obsolete_objects.drop_object('xyz')
How do I drop table/view/sequence name LIKE HZ% in fnd_tables/fnd_views/fnd_sequences tables from the database if it is marked as obsolete?
Execute this command from sqlplus.
execute fnd_drop_obsolete_objects.drop_object('HZ%')
The application runtime schema could be different from the database object owning schema for security reasons. To support this model as part of schema deployment, there is a mechanism to granularly provide grants on various database objects. These are granted to a set of fixed roles which are eventually available to runtime schema.
Privilege will be granted on the database object to the role based on privilege User defined properties defined for the object. Table 56-10, Table 56-11, Table 56-12, and Table 56-13 present the user defined properties that will be defined for each object type.
Table 56-10 Table Object Type Properties
| UDP Name | Description | Values |
|---|---|---|
|
|
Grant Insert Privilege on the table to the required role |
Y/N Default Y |
|
|
Grant Update Privilege on the table to the required role |
Y/N Default Y |
|
|
Grant Delete Privilege on the table to the required role |
Y/N Default Y |
|
|
Grant Select Privilege on the table to the required role |
Y/N Default Y |
|
|
The value of this UDP indicates whether a TRUNCATE statement is allowed on the table |
Y/N Default Y |
|
|
The value of this UDP indicates whether partitions can be maintained on the table. The ADM_DDL program when handling requests for dynamic DDL operations uses this information. |
Y/N Default Y |
|
|
The value of this UDP indicates whether it is possible to exchange partitions on the table. The ADM_DDL program when handling requests for dynamic DDL operations uses this information. |
Y/N Default Y |
|
|
The value of this UDP indicates whether it is possible to maintain indexes on the table. The ADM_DDL program when handling requests for dynamic DDL operations uses this information. |
Y/N Default Y |
Table 56-11 View Object Type Properties
| UDP Name | Description | Values |
|---|---|---|
|
|
Grant Insert Privilege on the view to the required role |
Y/N Default Y |
|
|
Grant Update Privilege on the view to the required role |
Y/N Default Y |
|
|
Grant Delete Privilege on the view to the required role |
Y/N Default Y |
|
|
Grant Select Privilege on the view to the required role |
Y/N Default Y |
Table 56-12 Sequence Object Type Properties
| UDP Name | Description | Values |
|---|---|---|
|
|
Grant Select Privilege on the sequence to the required role |
Y/N Default Y |
|
|
The value of this UDP indicates if the sequence can be reset to a specific value. The ADM_DDL program when handling requests for dynamic DDL operations uses this information. |
Y/N Default Y |
|
 Copyright © 2011, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|