C Oracle Data Integrator Driver for Complex Files Reference

The Oracle Data Integrator Driver for Complex Files (Complex File driver) allows Oracle Data Integrator to use complex files as data servers.


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.

This appendix includes the following sections:

Introduction to Oracle Data Integrator Driver for Complex Files

The Oracle Data Integrator Driver for Complex Files (Complex File driver) handles files in a Complex (or Native) Format as a JDBC data source. This allows Oracle Data Integrator to use complex files as data servers.

With the Complex File driver, Oracle Data Integrator can query complex files using standard SQL syntax and perform changes in the complex files. These operations occur within transactions and can be committed or rolled back.

The Oracle Data Integrator driver for Complex Files supports the following features:

  • Standard SQL (Structured Query Language) Syntax

  • Correlated subqueries, inner and outer joins


  • COUNT, SUM, MIN, MAX, AVG and other functions

  • Standard SQL functions

  • Transaction Management

  • Referential Integrity (foreign keys)

  • Saving changes into the complex files

Complex Files Processing Overview

The Complex File driver uses a Native Schema file. This file, written in the nXSD format describes the structure of the Native File and how to translate it to an XML file.

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 C-1.

Figure C-1 Complex File Driver Process

Description of Figure C-1 follows
Description of "Figure C-1 Complex File Driver Process"

The second part of the process, starting from the XML structure, corresponds precisely to the capabilities of the Oracle Data Integrator Driver for XML.

The Complex Files driver works in the following way:

  1. The complex file is translated to an intermediate XML file using the Native Schema (nXSD) file. Note that no physical file is created for the intermediate XML file but a streaming XML structure.

  2. The driver loads the XML structure and data into a relational schema, using a XML to SQL Mapping.

  3. The user works on the relational schema, manipulating data through regular SQL statements or specific driver commands for driver operations.

  4. Upon disconnection or user request, the Complex Files driver synchronizes the data and structure stored in the schema back to the complex file.

Generating the Native Schema

The Native Schema can be created manually, or generated using the Native Format Builder Wizard available as part of Fusion Middleware Technology Adapters. See Native Format Builder Wizard in the User's Guide for Technology Adapters for more information on the Native Schema format and the Native Format Builder Wizard.

XML to SQL Mapping

The XML to SQL Mapping is a complex process that is used to map a hierarchical structure (XML) into a relational structure (schema). This mapping is automatic. See XML to SQL Mapping for more information.

JSON Support

Flat files in JSON format are supported through the nXSD format. The nXSD file can be created manually or through the Native Format Builder Wizard (See Generating the Native Schema for details). If an XSD file with no nXSD annotation is used, you need to provide additional JDBC property: tt=json or translator_type=json, which will enable the driver to use the JSON translator for parsing the input file.

Supported Features

The Complex File driver supports the same features as the XML driver:

  • Schema Storage in a built-in engine or external database is supported in the same way as the XML Driver. See Schema Storage and Using an External Database to Store the Data for more information.

  • Multiple Schemas are supported, with the following differences:

    • Only a single schema can be created at connection time, based on the Native Schema file.

    • Parameters allowing creating multiple schemas at connection time as indicated in Automatically Create Multiple Schemas are not supported. This includes add_schema_bundle, add_schema_path, and addschema_X.

    • Additional schemas can be created after the connection using the CREATE SCHEMA and LOAD FILE commands.

  • Case-sensitivity is managed similarly to the XML driver. See Case Sensitivity for more information.

  • Loading/Synchronizing with the Complex File driver works the same way as the XML Driver. Loading/Synchronizing operations automatically propagate to the Native file. See Loading/Synchronizing for more information.

  • Locking is supported. When connected, the complex file is locked and when disconnected, it is unlocked. The UNLOCK FILE command is supported.

Driver Configuration

The Oracle Data Integrator driver for Complex Files is automatically installed with Oracle Data Integrator. The following topics cover advanced configuration topics and reference information.

This section details the driver configuration.

  • The driver name is: oracle.odi.jdbc.driver.file.complex.ComplexFileDriver

  • The URL Syntax is: jdbc:snps:complexfile?f=<native file location>&d=<native schema>&re=<root element name>[&s=<schema name>&<property>=<value>...]

The properties for the URL are detailed in Oracle Data Integrator Driver for Complex Files Reference.

Table C-1 Driver Properties

Property Mandatory Type Default Description

file or f


string (file location)


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.

dtd or d


string (file location)


Native Schema (nXSD) file location. This parameter is mandatory.

root_elt or re




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.

read_only or ro


boolean (true | false)


Open the native file in read only mode.

schema or s




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.

Note: It is not possible to make more than one connection to a schema. Subsequent connections fail if trying to connect to a schema already in use.

Important: The schema name should be specified in uppercase, and cannot be named like an existing XML element.



boolean (true | false)


If this option is set to true, the schema for this connection is completely isolated from all other schemas. With this option, you can specify the same schema name for several connections, each schema being kept separated. When using this option, tables in this schema cannot be accessed from other schemas, and this connection cannot access tables from other schemas.

Note: This option is not applicable when an external database is used.

translator_type or tt


string (json)


If this option is set to json, the xsd does not require nXSD annotations and will automatically use the JSON translator for parsing the input file.

db_props or dp




This property is used to use an external database instead of the memory engine to store the schema.

See Using an External Database to Store the Data for more information.

load_data_on_connect or ldoc


boolean (true | false)


Automatically load the data in the schema when performing the JDBC connection. If set to false, a SYNCHRONIZE statement is required after the connection to load the data.

This option is useful to test the connection or browse metadata without loading all the data.

drop_on_disconnect or dod


boolean (true | false)


Automatically drop the schema when closing the JDBC connection.

If true, the schema is stored in the built-in engine, it is always dropped.

If the schema is stored in an external database, the driver attempts to drop the database schema, but might fail if tables still exist in this schema. The drop_tables_on_drop_schema property can be specified in the external database property file to ensure that all tables are automatically dropped when the schema is dropped. See Using an External Database to Store the Data for more information.



boolean (true | false)


When this property is set to true, elements for which maxOccurs is not specified in the schema are considered as maxOccurs ="unbounded". Otherwise, the driver assumes that maxOccurs=1 when maxOccurs is not specified.

java_encoding or je


string (encoding code)


Target file encoding (for example: ISO8859_1). You will find a list of supported encoding at the following URL: https://docs.oracle.com/javase/8/docs/technotes/guides/intl/encoding.doc.html.

numeric_ids or ni


boolean (true | false)


If set to true, all internal Primary and Foreign Keys are of NUMERIC type. Otherwise, they are of the VARCHAR type.

id_length or il



10 / 30

The length of the internal Primary and Foreign Key columns. The default is 10 for NUMERIC column types and 30 for VARCHAR column.

numeric_scale or ns




Scale of the numeric columns generated in the relational schema.

no_batch_update or nobu


boolean (true | false)


Batch update is not used for this connection. The command to set the batch update is not sent. This prevents errors to occur for external databases that do not support this JDBC feature, or allows to debug errors related to batch update usage.

transform_nonascii or tna


boolean (true|false)


Transform Non Ascii. Set to false to keep non-ascii characters.

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.


Detailed Driver Commands

The Complex File driver supports the same driver commands as the XML driver. See Detailed Driver Commands for the driver commands supported by the XML Driver.

The exceptions to this rule are the following:

  • In the Complex File driver syntax, the commands that are related to the XML file such as CREATE FILE or LOAD FILE, are applied to the Native File. For example, the command CREATE FILE creates a native format file from the schema content.

  • VALIDATE is not supported.

  • CREATE FILE is supported but the NO_CLOSING_TAGS and NO_DEFAULT_NS parameters are ignored.

  • CREATE SCHEMA requires the WITH DTD parameter.

  • LOAD FILE requires the WITH DTD parameter.

JDBC API and XML Schema Supported Features

The Complex File driver supports the same JDBC features as the XML driver. See SQL Syntax for more information.