C Oracle Data Integrator Driver for Complex Files Reference

This appendix describes how to work with the Oracle Data Integrator driver for Complex Files.

This appendix includes the following sections:

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

  • ORDER BY and GROUP BY

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

  • Standard SQL functions

  • Transaction Management

  • Referential Integrity (foreign keys)

  • Saving changes into the complex files

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

C.2.1 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 Oracle Fusion Middleware User's Guide for Technology Adapters for more information on the Native Schema format and the Native Format Builder Wizard.

C.2.2 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 Section B.2.1, "XML to SQL Mapping" for more information.

C.2.3 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 Section B.2.3.1, "Schema Storage" and Section B.3.3, "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 Section B.3.2, "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 Section B.2.3.4, "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 Section B.2.3.5, "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.

C.3 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 Table C-0.

Table C-1 Driver Properties

Property Mandatory Type Default Description

file or f

Yes

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

Yes

string (file location)

-

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

root_elt or re

Yes

String

-

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

No

boolean (true | false)

false

Open the native file in read only mode.

schema or s

No

string

-

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.

standalone

No

boolean (true | false)

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.

db_props or dp

No

string

-

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

See Section B.3.3, "Using an External Database to Store the Data" for more information.

load_data_on_connect or ldoc

No

boolean (true | false)

true

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_disc or dod

No

boolean (true | false)

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 Section B.3.3, "Using an External Database to Store the Data" for more information.

useMaxValue

No

boolean (true | false)

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

No

string (encoding code)

UTF8

Target file encoding (for example: ISO8859_1). You will find a list of supported encoding at the following URL: http://java.sun.com/j2se/1.3/docs/guide/intl/encoding.doc.html.

numeric_id or ni

No

boolean (true | false)

true

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

No

integer

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

No

integer

empty

Scale of the numeric columns generated in the relational schema.

no_batch_update or nobu

No

boolean (true | false)

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.

log_file or lf

No

string (file location)

-

Log file name. If the log file is empty, the trace is displayed in the standard output.

The presence of this property triggers the trace for the schema. Each schema may have a different trace file.

log_level or ll

No

Integer

-

Log level. The log level is a mask of the following values:

  • 1: Important internal events

  • 2: Detailed internal events

  • 4: Native SQL commands

  • 8: XML-Relational mapping calculation

  • 16: XML-Relational mapping validation (Table names changes, etc)

Examples:

  • Trace Important & Detailed internal events: log_level=3 (1+2)

  • Trace Native SQL commands and Important internal events: log_level=5 (1+4)

  • Trace XML-Relational mapping calculation and validation: log_level=24 (16+8)

  • Trace all events: log_level=31 (1+2+4+8+16)

tna

No

boolean (true|false)

true

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.

jdbc:snps:complexfile?f=/infiles/PROD20100125_001.csv&d=/infiles/products.nxsd&re=root&s=PRODUCTS

C.4 Detailed Driver Commands

The Complex File driver supports the same SQL syntax as the XML driver. See Section B.5, "SQL Syntax" for the SQL Syntax 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_TAG and NO_DEFAULT_NS parameters are ignored.

  • CREATE SCHEMA requires the WITH DTD parameter.

  • LOAD FILE requires the WITH DTD parameter.

C.5 JDBC API and XML Schema Supported Features

The Complex File driver supports the same JDBC features as the XML driver. See Section B.5, "SQL Syntax" for more information.