This chapter describes how to work with Complex Files in Oracle Data Integrator.
This chapter includes the following sections:
Section 6.5, "Creating and Reverse-Engineering a Complex File Model"
Oracle Data Integrator supports several files types. This chapter describes how to work with the Complex (or native) File format. See Chapter 3, "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 "Native Format Builder Wizard" in the Oracle Fusion Middleware User's Guide for Technology Adapters for more information on the Native Schema format, and Appendix C, "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 6-1.
Figure 6-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.
Note that for simple flat files formats (fixed and delimited), it is recommended to use the File technology, and for XML files, the XML technology. See Chapter 3, "Files" and Chapter 5, "XML Files" for more information.
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 Chapter 4, "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.
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):
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
Setting up the topology consists in:
A Complex File data server corresponds to one native file that is accessible to Oracle Data Integrator.
Create a data server for the Complex File technology using the standard procedure, as described in "Creating a Data Server" of the Oracle Fusion Middleware Developer's Guide for 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:
jdbc:snps:complexfile?f=<native file location>&d=<native schema>&re=<root element name>&s=<schema name>[&<property>=<value>...]
Table 6-1 lists the key properties of the Oracle Data Integrator Driver for Complex Files. These properties can be specified in the JDBC URL.
See Appendix C, "Oracle Data Integrator Driver for Complex Files Reference" for a detailed description of these properties and for a comprehensive list of all properties.
Table 6-1 Complex File Driver Properties
<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 mandatory.
This schema will be selected when creating the physical schema under the Complex File data server.
The following example illustrates these properties:
Connects to the
PROD20100125_001.csv file described by
products.nxsd and expose this file as a relational structure in the PRODUCT Schema.
Create a Complex File physical schema using the standard procedure, as described in "Creating a Physical Schema" of the Oracle Fusion Middleware Developer's Guide for 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" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator and associate it in a given context.
Setting up a project using the Complex File technology follows the standard procedure. See "Creating an Integration Project" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
It is recommended to import the following knowledge modules into your project for getting started:
LKM SQL to SQL
LKM File 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.
This section contains the following topics:
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 the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
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 the Oracle Fusion Middleware Developer's Guide for 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 Section 5.5.2, "Reverse-Engineering an XML Model" and XML to SQL Mapping for more information.
You can use a complex file as a source or a target of an integration interface.
The KM choice for an interface or a check determines the abilities and performances of this interface 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 interfaces 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.
See Section 5.6, "Designing an Interface" in Chapter 5, "XML Files" for more information.