This chapter includes the following sections:
Oracle Data Integrator supports several files types. This chapter describes how to work with the Complex (or native) File format. See Files for information about simple fixed or delimited files containing ASCII or EBCDIC data.
For complex files it is possible to build a Native Schema description file that describes the file structure. Using this Native Schema (nXSD) description and the Oracle Data Integrator Driver for Complex Files, Oracle Data Integrator is able to reverse-engineer, read and write information from complex files.
See Building a Native Schema Description File Using the Native Format Builder for information on how to build a native schema description file using the Native Format Builder Wizard, and Oracle Data Integrator Driver for Complex Files Reference for reference information on the Complex File driver.
The Oracle Data Integrator Driver for Complex Files (Complex File driver) converts native format to a relational structure and exposes this relational structure as a data model in Oracle Data Integrator.
The Complex File driver translates internally the native file into an XML structure, as defined in the Native Schema (nXSD) description and from this XML file it generates a relational schema that is consumed by Oracle Data Integrator. The overall mechanism is shown in Figure 8-1.
Figure 8-1 Complex File Driver Process
Description of "Figure 8-1 Complex File Driver Process"
Most concepts and processes that are used for Complex Files are equivalent to those used for XML files. The main difference is the step that transparently translates the Native File into an XML structure that is used internally by the driver but never persisted.
The Complex File technology concepts map the Oracle Data Integrator concepts as follows: A Complex File corresponds to an Oracle Data Integrator data server. Within this data server, a single schema maps the content of the complex file.
The Oracle Data Integrator Driver for Complex File (Complex File driver) loads the complex structure of the native file into a relational schema. This relational schema is a set of tables located in the schema that can be queried or modified using SQL. The Complex File driver is also able to unload the relational schema back into the complex file.The relational schema is reverse-engineered as a data model in ODI, with tables, columns, and constraints. This model is used like a standard relational data model in ODI. If the modified data within the relational schema needs to be written back to the complex file, the driver provides the capability to synchronize the relational schema into the file.
Pre/Post Processing Support for Complex File Driver
You can now customize the way data is fed to the Complex File driver. You can set up intermediate processing stages to process the data that is retrieved from an external endpoint using Oracle Data Integrator, or to write the data out to an external endpoint.
For detailed information about configuring and implement the pre and post processing stages for Complex File driver, see Pre/Post Processing Support for XML and Complex File Drivers.
You can use a Complex File data server as any SQL data server. Complex File data servers support both the technology-specific KMs sourcing or targeting SQL data servers, as well as the generic KMs. See Generic SQL or the technology chapters for more information on these KMs.
You can also use the IKM XML Control Append when writing to a Complex File data server. This Knowledge Module implements specific option to synchronize the data from the relational schema to the file, which is supported by the Complex File driver.
Installation and Configuration
Make sure you have read the information in this section before you start working with the Complex File technology:
Before performing any installation you should read the system requirements and certification documentation to ensure that your environment meets the minimum installation requirements for the products you are installing.
The list of supported platforms and versions is available on Oracle Technical Network (OTN):
Technology Specific Requirements
There are no technology-specific requirements for using Complex Files in Oracle Data Integrator.
This section lists the requirements for connecting to complex files.
Oracle Data Integrator Driver for Complex Files
Complex files are accessed through the Oracle Data Integrator Driver for Complex File. This JDBC driver is installed with Oracle Data Integrator and requires no other installed component or configuration.
You must ask the system administrator for the following connection information:
The location of the Native Schema (nXSD) file associated with your native file
The location of the native complex file
Building a Native Schema Description File Using the Native Format Builder
You can build a Native Schema (nXSD) description file using the Native Format Builder Wizard. You can start the Native Format Builder Wizard from the Data Server Editor when creating the Complex File data server.
To build a native schema description file using the native format builder:
- In the Topology Navigator expand the Technologies node in the Physical Architecture accordion.
- Select the Complex File technology.
- Right-click and select New Data Server.
- In the JDBC tab, click the Edit nXSD button. The Native Format Builder Wizard appears.
- Follow the on-screen instructions and complete the Native Format Builder Wizard to create a Native Schema description file.
See Native Format Builder Wizard in the User's Guide for Technology Adapters, for more information on the Native Schema format.
Setting up the Topology
Setting up the topology consists in:
Creating a Complex File Data Server
A Complex File data server corresponds to one native file that is accessible to Oracle Data Integrator.
The use of the In-Memory Engine is not a best practice, and may cause issues, when using the Complex File Technology.
Cause of the error: In-memory HSQL is not recommended for production use. It has memory leaks, beyond the scope of ODI that will eventually bring the JVM down. It is only meant for development use. It is recommended to switch to external DB such as Oracle, MySQL, or MS SQLServer.
Creation of the Data Server
Create a data server for the Complex File technology using the standard procedure, as described in Creating a Data Server of Administering Oracle Data Integrator. This section details only the fields required or specific for defining a Complex File data server:
- In the Definition tab:
Name: Name of the data server that will appear in Oracle Data Integrator.
User/Password: These fields are not used for Complex File data servers.
- In the JDBC tab, enter the following values:
Edit nXSD: Launch the Native Format Builder Wizard if you want to create a Native Schema description file.
For more information on Native Format Builder Wizard, see Building a Native Schema Description File Using the Native Format Builder.
Properties: Configure the properties, such as native file location, native schema, root element, and schema name, for the Oracle Data Integrator Driver for Complex Files.
Table 8-1 lists the key properties of the Oracle Data Integrator Driver for Complex Files. These properties can be specified in JDBC URL.
See Oracle Data Integrator Driver for Complex Files Reference for a detailed description of these properties and for a comprehensive list of all properties.
Table 8-1 Complex File Driver Properties
Property Value Notes
<native file name>
Native file location. Use slash "/" in the path name instead of back slash "\". It is possible to use an HTTP, FTP or File URL to locate the file. Files located by URL are read-only. This parameter is mandatory.
Native Schema (nXSD) file location. This parameter is mandatory.
Name of the element to take as the root table of the schema. This value is case sensitive. This property can be used for reverse-engineering for example a specific section of the Native Schema. This parameter is mandatory.
Name of the relational schema where the complex file will be loaded. This parameter is optional.
This schema will be selected when creating the physical schema under the Complex File data server.
Creating a Complex File Physical Schema
Create a Complex File physical schema using the standard procedure, as described in Creating a Physical Schema in Administering Oracle Data Integrator.
The schema name that you have set on the URL will be preset. Select this schema for both the Data Schema and Work Schema.
Create for this physical schema a logical schema using the standard procedure, as described in Creating a Logical Schema in Administering Oracle Data Integrator and associate it in a given context.
Setting Up an Integration Project
Setting up a project using the Complex File technology follows the standard procedure. See Creating an Integration Project of Developing Integration Projects with Oracle Data Integrator.
It is recommended to import the following knowledge modules into your project for getting started:
LKM SQL to SQL
IKM XML Control Append
In addition to these knowledge modules, you can also import file knowledge modules specific to the other technologies involved in your product.
Creating and Reverse-Engineering a Complex File Model
This section contains the following topics:
Create a Complex File Model
A Complex File model groups a set of datastores. Each datastore typically represents an element in the intermediate XML file generated from the native file using the native schema.
Create a Complex File model using the standard procedure, as described in Creating a Model of Developing Integration Projects with Oracle Data Integrator.
Reverse-engineer a Complex File Model
The Complex File technology supports standard reverse-engineering, which uses only the abilities of the Complex File driver.
To perform a Standard Reverse- Engineering with a Complex File model use the usual procedure, as described in Reverse-engineering a Model of Developing Integration Projects with Oracle Data Integrator.
This reverse-engineering uses the same process as the reverse-engineering of XML Files. The native schema (nXSD) provided in the data server URL is used as the XSD file to describe the XML structure. See Reverse-Engineering an XML Model and XML to SQL Mapping for more information.
Designing a Mapping
You can use a complex file as a source or a target of a mapping.
The KM choice for a mapping or a check determines the abilities and performances of this mapping or check. The recommendations below help in the selection of the KM for different situations concerning a Complex File data server.
Complex File data models are handled in mappings similarly to XML structures. For example, the Synchronization model is the same for complex files and XML files and the same knowledge modules can be used for both technologies.