XML Definition File Utilities

Overview

The XML Definition File (XDF) utility is the next generation version of the older Object Definition File (ODF) utility which was used to propagate object definitions.

Note that XDF currently does not support "alters" for nested tables. Only "create" scenarios for nested tables is supported. Beginning with Release 12.1, XDF can capture the nested table information in the XDF file, read this information while propagating the table object, and alter the base table to include this nested table column.

According to Oracle E-Business Suite standards, there cannot be two primary keys on a table both with the primary key type as 'D'. Beginning with Release 12.1, the XDF application will, depending on a specified parameter value, overwrite the old primary key and create a new one in the XDF file.

XDF Generation Utility

The XDF (XML Definition File) Generation Utility is used in extracting schema object definitions into XDF format (.xdf files).

The .xdf extension is to distinguish XML files containing object definitions from other XML files that contain data. The XDF Generation Utility must be run against an Oracle 9iR2 database (v9.2.0.3 or above) or higher, given that it relies on the Metadata API (dbms_metadata) to generate the definitions in XML. The standard location for XDF files is $PROD_TOP/admin/xdf or $PROD_TOP/patch/115/xdf.

The format of the XDF file is such that a primary object along its dependent object(s) are associated together in one file. For example, the definition of a table along with all its indexes, constraints and triggers will be associated together in a single XDF file.

Please refer to the section on XDF file structures based on object types. The XDF Generation Utility is available in Perl() and Java (FndXdfGen). The XDF Generation Perl utility is located in the central tools area under $FND_TOP/bin.

Features

The XDF Generation Utility augments the base definition generated by the Metadata API and has the following features:

• Auto-generation of XDF filenames based on the name of the primary object

• Standard, more granular XDF structure where the definition of a primary object is associated with relevant dependent objects (Refer to Object Containment in XDF.)

• Backward compatibility support for ODF features such as deferred indexes, Drop/Create constraints, ODF default

• DBDRV hint generation for all objects

• Captures database comments on tables, views, materialized views, and their columns

• Generates tags for Oracle E-Business Suite specific metadata such as:

  • Tablespace classification

  • Logical Primary Keys (PKs) and PK columns

  • Logical Foreign Keys (FKs) and FK columns

  • Histogram information

  • Translation-related information

Important: Constraints on tables must have user-defined names. They must not have system-generated names.

XDF Generation

XDF Generation Process

Developers can use SQL to create or alter database objects, and then invoke the XDFGEN utility to produce an object definition file in XML.

The XDF file always contains the complete definition of the primary object and dependent object(s). After the initial generation of the XDF file, any subsequent changes, regardless of whether they are specific only to the primary object and/or dependent object(s), will always result in the complete definition of the primary and dependent object(s) being generated. At present, the metadata API generates only the complete definition and does not support incremental definitions.

Object Containment in XDF

The ADODFGEN utility relied on the concept of building blocks to generate ODF(s). XDF does not support the concept of building blocks and instead groups a primary object along with its dependent objects to allow for a blend of granularity, ease of maintenance, and efficient comparison. The following table provides details on object containment:

Object Containment Details

Primary Object Types	Details	Dependent Objects	Containment in XDF
Tables	These include Global temporary tables, index-organized tables or IOTs, queue tables, normal tables, tables with types (for example, VARRAYS and nested tables types, object types, spatial data-types, partitioned tables	Indexes, triggers (optional), types, constraints, sequences, policies	One Table and its dependent object definitions per XDF
Materialized Views (MVs) on Materialized Views		Indexes, triggers (optional), Materialized view logs	One MV and its dependent object definitions per XDF
Materialized Views on Base Tables		Indexes, triggers (optional)	One MV and its dependent object definitions per XDF
Materialized View Logs on Base Tables		Indexes, triggers (optional), constraints	One MV log and its dependent object definitions per XDF
Views			One view definition per XDF
Advanced Queues			One queue definition per XDF
Triggers	Triggers could be generated along with the Table definition or separately.		One or more trigger definition(s) per XDF
ADT (Object or Abstract Datatypes)			One ADT definition per XDF

Usage

The XDF Generation and Comparison utilities are available as Java standalone command-line utilities. They are also available as Perl wrappers, xdfgen.pl and xdfcmp.pl (see usage below) over the Java FndXdfGen and FndXdfCmp utilities, respectively, to improve the ease of use for developers.

Developers can use any Release 12.2 environment to extract latest schema definitions of the database objects in XDF files.

Ensure that the latest generation patch has been applied on your environment and set the required environment variables like $FND_TOP and $CLASSPATH to point to this environment before generating the XDF.

Once the XDF file is generated, you can test the upload of this by invoking xdfcmp.pl usage notes to test your changes.

Perl Wrapper Utilities

The following parameters, while mandatory in the Java utilities, are optional in the Perl wrapper utilities, xdfgen.pland xdfcmp.pl:

• xsl_directory - Java cannot read environment variables, which bring in a OS dependency. (In addition, the setEnv() method has been deprecated). With Perl, this environment variable can be set to the default and read internally, without having the developer to provide it.

• java class invocation with complete directory/package path - There is no alternative option for this in Java. With Perl, the commands xdfgen.pl and xdfcmp.pl can be invoked without any references to packages and so on.

• jdbc_connstring - This can be simplified to require just the DB SID. Internally, there is a call made to tnspingand the hostname and port information are retrieved from tnsping.

Java Wrapper Utilities

The following parameters are now optional in the Java utilities:

• object_type - With the help of the object name, the object type is internally determined in XDFGEN. In cases of ambiguity due to presence of multiple objects with same names, an error is thrown indicating that object_type or owner_app_shortname are required.

• owner_app_short_name - With the help of the object name, the owner_app_shortname is internally determined in XDFGEN and displayed in the output. In cases of ambiguity due to presence of multiple objects with same names, an error is thrown indicating that object_type or owner_app_shortname are required.

JDeveloper Invocation

XDF Generation and Comparison utilities can also be invoked from JDeveloper:

1. Download Oracle JDeveloper 10g (Version 9.0.5.2, build 1618). This specific version is required for XML Parser compatibility.

2. Copy XML parser xmlparserv2-904.zip to <jdev-dir>\jdev\lib\patches.

3. Download XDFGen.jar and XDFCmp.jar to <jdev-dir>\jdev\lib\ext(mouse right-click and use "Save Target As..." link).

4. Start JDeveloper. Create or open an application workspace and project (an empty project is acceptable as well) and define database connections for the source and target database(s). Select project. The XDF Generation Wizard and XDF Comparison Wizard can be selected from the Tools menu.

XDF Comparison Utility

The XDF (XML Definition File) Comparison Utility (Perl and Java) compares database object definitions contained in the XDF with the actual database object definitions in the target database and then applies the changes to the object in the target database accordingly.

The XDF Comparison Utility can run against database versions 8.1.7 and higher. The XDF Comparison Utility is available in Perl (xdfcmp.pl) and Java (FndXdfCmp). The XDF Comparison Perl Utility is located in the central tools area under $FND_TOP/bin.

Features

The XDF Comparison Utility also contains the following features:

• Support for new and old tablespace models and integration with the AD Tablespace API.

• Support for the dictionary and locally managed tablespaces.

• Propagation of database comments for tables/views/materialized views at the target database.

• Support for automatic drop/recreate of indexes and materialized views (MVs) logs on MVs.

• Integration with the AD serialization API for materialized views to take care of exclusive locks on materialized views being created in parallel.

• Granting privileges on the objects to the APPS user and creating synonyms in the APPS schema.

• Updating the FND/AOL Data Dictionary for schema object definition-related changes.

The following are FND/AOL Data Dictionary tables updated by the XDF Comparison Utility:

• FND_TABLES

• FND_COLUMNS

• FND_VIEWS

• FND_FOREIGN_KEYS

• FND_PRIMARY_KEYS

• FND_HISTOGRAM_COLS

• FND_FOREIGN_KEY_COLUMNS

• FND_PRIMARY_KEY_COLUMNS

• FND_OBJECT_TABLESPACES

Supported Objects

The following is a list of database objects supported by XDF.

• Tables

  In addition to normal tables, XDF supports the following types of tables:

  • Global Temporary Tables.

  • Temporary Tables. Temporary Tables are created in the schema of the product team creating the table.

  • Global Temporary Tables. Global Temporary Tables cannot be altered or dropped if they are in use by another session. DDL statements on these tables must be executed during a phase in which no DML statements are run against the tables.

  • Queue Tables.

  • Partitioned Tables.

  • Index Organized Tables (IOTs).

• Object Datatypes (Abstract Datatypes, or ADTs)

  Object types are schema objects. They are abstractions of the real-world entities (for example, purchase orders) that application programs deal with. There are two categories of object datatypes:

  • Object types

  • Collection types

  Object datatypes use the built-in datatypes and other user-defined datatypes as the building blocks for datatypes that model the structure and behavior of data in applications.

  Each collection type describes a data unit made up of an indefinite number of elements, all of the same datatype. The collection types are array types and table types. The corresponding data units are called VARRAYs and nested tables.

  XDF alters the ADT in the following cases:

  • Storage parameters change

  • Filter Column

  XDF drops and recreates the ADT in the following cases:

  • ROWID to Primary Key and vice versa

  • Filter Columns in Target database, but not in XDF

  The XDF Structure for Objects or Abstract Datatypes is described in the following table:

  XDF Structure for Objects or Abstract Datatypes

  Primary Object Types	Primary/Dependent	Dependent Objects	Containment in XDF	Filename
  Abstract datatypes or object datatypes	Primary	N/A	One or more ADT definition(s) per XDF Note: Recommended approach is to have one object datatype per XDF.	<Object Datatype name>.xdf

• (Partitioned) Materialized Views

• (Partitioned) Materialized View Logs

• (Partitioned) Indexes on Materialized Views and Materialized View Logs

• Global Temporary Tables

• Function-based Indexes

• Partitioned Tables and Indexes

• Index-Organized Tables

• Advanced Queues

• Advanced Queue Tables

• Application Contexts

• Indexes including B*tree, Bitmap, and Hash, in addition to function-based indexes and partitioned indexes listed above.

• Views

• Sequences

• Triggers

• Constraints

• Policies

Usage

The following notes apply to using the XDF Comparison Utility.

XDF Comparison PERL Utility (xdfcmp.pl)

xdfcmp.pl <owner_schema>@<oracle_sid> <xdf_filename>   
[<apps_schema_name>]  
[logfile=<filename>]           
[changedb=<y|n>]  
[deferred=<list>]  
[dropCr=<list>]  
[Data_Sec_Vpd=<y|Y>]  
[Standalone=<y|Y>]          

Enter the password when prompted.

You have the option to enter the password on the command line, but this method may be insecure.

Mandatory arguments:

• apps_schema - ORACLE schema name of APPS schema.

• oracle_sid - ORACLE SID of the target database where the XDF needs to be compared and applied against the XDF file.

• xdf_filename - The name of XDF file, containing the primary object to be applied to and/or compared with the target database.

Optional parameters:

• apps_password - ORACLE schema password of APPS schema.

• apps_schema_name/password - The APPS schema name and password needs to be specified if it is not the default value of apps/apps.

• changedb - The default is 'changedb=y'. If the changedb is specified as 'n' then the SQL statements generated by the comparison utility are not executed but displayed on the standard output or a log file.

• deferred - This parameter is to provide backward compatibility support for the existing feature in ODFv1 to defer creation of indexes until later. This option is relevant in context of AD AutoPatch. To specify which indexes to defer, use the option: 'deferred=<comma-separated list of indexes to defer>' or if all indexes have to be deferred then 'deferred=all'.

• dropCr - XDF supports dropping constraints and recreating them in a later phase. This option is relevant in context of AD AutoPatch The list of constraints that have to be dropped and recreated are specified as 'dropCr=<comma separated list of constraints>'.

• logfile - The output of the generator program is written to standard out. Specify a logfile name if the output is to be written to a log file.

• Data_Sec_Vpd - This parameter is used to specify that a service security synonyms or view has to be created dynamically based on the database version. If the database version is 9 then a synonym is created; else a view is created. Permitted values are 'data_sec_vpd=y'

• Standalone - This option is used to execute the XDF comparison utility in a standalone mode. Permitted values are 'standalone=y'.

Examples

Here are some examples of xdfcmp.pl usage.

• Global temporary table - The following command is used to compare a table definition in the XDF file wftmptab1.xdf with the definition in the database db_name and examine the differences.

  xdfcmp.pl <APPLSYS username>@db_name wf_local_users_temp.xdf changedb=n     

• Table - The following command is used to compare a table definition in the XDF file jta_sync_contact_mapping.xdf with the definition in the database db_name and run SQL statements to remove the differences such that the object definition in the database matches the definition in the XDF file. In this example, the index JTA_SYNC_CONTACT_MAPPING_U1 on the table is deferred.

  xdfcmp.pl <JTF user>@db_name jta_sync_contact_mapping.xdf deferred=JTA_SYNC_CONTACT_MAPPING_U1     

• Materialized view - The following command is used to compare a materialized view definition in the XDF file bil_bi_opty_t_mv.xdf with the definition in the database db_name and run SQL statements to remove the differences such that the object definition in the database matches the definition in the XDF file.

  xdfcmp.pl <APPS username>@db_name bil_bi_opty_t_mv.xdf     

• View - The following command is used to compare a view definition in the XDF file abmbg_act_acct_data_var_v.xdf with the definition in the database db_name and run SQL statements to remove the differences such that the object definition in the database matches the definition in the XDF file.

   xdfcmp.pl <APPS username>@db_name abmbg_act_acct_data_var_v.xdf

XDF Comparison Java Utility ((FndXdfCmp)

java oracle.apps.fnd.odf2.FndXdfCmp 
<schemaId> 
[<schemaPassword>] 
<appId>
[<appPassword>] 
<jdbc driver type> 
<jdbc tns info> 
<object_type> 
<xdf file name> 
<xsl file directory> 
[deferred=<list> ] 
[dropCr=<list>] 
[logfile=<filename>] 
[Data_Sec_Vpd=<y|Y>] 
[Standalone=<y|Y>] 
[changedb=<y|n>] 

You have the option to enter the passwords on the command line, but this method may be insecure. If you omit the passwords, enter them when prompted.

Note: The CLASSPATH environment variable has to be set with the correct JDK and JDBC versions (at least jdk118 and jdbc111) before running the XDF Comparison program.

Mandatory arguments:

• schema_name - ORACLE schema name in which the object exists or should be created.

• apps_schema - ORACLE schema name of current APPS schema.

• jdbc_protocol - JDBC protocol (thin or oci8).

• jdbc_connect_string - JDBC TNS information either in format of Net8 connect string enclosed in double quotes or hostname:port:oracle_sid format.

• object_type - The object type information – table, qtable, mview, mviewlog, sequence, type, trigger, view, or policy.

• xdf_filename - The XDF file name, which contains the object definition.

• xsl_directory - The xsl directory, which contains all the xsl files required for XSLT transformation. It is usually $FND_TOP/patch/115/xdf/xsl or $FND_TOP/admin/xdf/xsl.

Optional Parameters:

• schema_pwd - ORACLE schema password in which the object exists or should be created.

• apps_pwd - ORACLE schema password of current APPS schema.

• deferred - XDF supports deferring creation of some indexes until later. The list of indexes that have to be deferred are specified as deferred=<comma-separated list of indexes to defer>. This option is relevant in context of AD AutoPatch. It is relevant for the standalone utility usage only when used in conjunction with constraints.

• dropCr - XDF supports dropping constraints and recreating them in a later phase. The list of constraints that have to be dropped and recreated are specified as dropCr=<comma separated list of constraints>.

• logfile - The output of the comparison utility is written to standard out. Specify a logfile name if the output is to be written to a log file.

• Data_Sec_Vpd - This parameter is used to specify that a service security synonyms or view has to be created dynamically based on the database version. If the database version is 9 then a synonym is created else a view is created. Permitted values are data_sec_vpd=y

• Standalone - This option is used to execute the XDF comparison utility in a standalone mode. Permitted values are standalone=y

• changedb - The default is changedb=y. If the changedb is specified as 'n' then the SQL statements generated by the comparison utility are not executed but displayed on the standard output or a log file.

Examples:

Global temporary table - The following command is used to compare a table definition in the XDF file wf_local_users_temp.xdf with the definition in the database db_name and run SQL statements to remove the differences so that the object definition in the database matches the definition in the XDF file.

java oracle.apps.fnd.odf2.FndXdfCmp applsys thin ap123fam:1521:db_name table /home/jdoe/wf_local_users_temp.xdf /fnddev/fnd/11.5/patch/115/xdf/xsl     

Table - The following command is used to compare a table definition in the XDF file jtasyncmap.xdf with the definition in the database db_name and run SQL statements to remove the differences so that the object definition in the database matches the definition in the XDF file. In this example, the index JTA_SYNC_CONTACT_MAPPING_U1 on the table is deferred.

java oracle.apps.fnd.odf2.FndXdfCmp jtf thin ap123fam:1521:db_name table jta_sync_contact_mapping.xdf /fnddev/fnd/11.5/patch/115/xdf/xsl deferred=JTA_SYNC_CONTACT_MAPPING_U1     

Materialized view - The following command is used to compare a materialized view definition in the XDF file bil_bi_opty_t_mv.xdf with the definition in the database db_name and run SQL statements to remove the differences so that the object definition in the database matches the definition in the XDF file.

java oracle.apps.fnd.odf2.FndXdfCmp apps thin ap123fam:1521:db_name mview bil_bi_opty_t_mv.xdf /fnddev/fnd/11.5/patch/115/xdf/xsl     

View - The following command is used to compare a view definition in the XDF file aview.xdf with the definition in the database db_name and run SQL statements to remove the differences so that the object definition in the database matches the definition in the XDF file.

 java oracle.apps.fnd.odf2.FndXdfCmp apps thin ap123fam:1511:db_name view abmbg_act_acct_data_var_v.xdf /fnddev/fnd/11.5/patch/115/xdf/xsl