Oracle Data Integrator provides the Knowledge Modules (KM) listed in the following table for handling Oracle data. The KMs use Oracle specific features. It is also possible to use the generic SQL KMs with the Oracle Database. See Generic SQL for more information.

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):

http://www.oracle.com/technetwork/middleware/data-integrator/documentation/index.html.

Some of the Knowledge Modules for Oracle use specific features of this database. This section lists the requirements related to these features.

This section lists the requirements for connecting to an Oracle Database.

JDBC Driver

Oracle Data Integrator is installed with a default version of the Oracle Type 4 JDBC driver. This drivers directly uses the TCP/IP network layer and requires no other installed component or configuration.

It is possible to connect an Oracle Server through the Oracle JDBC OCI Driver, or even using ODBC. For performance reasons, it is recommended to use the Type 4 driver.

Connection Information

You must ask the Oracle DBA the following information:

• Network Name or IP address of the machine hosting the Oracle Database.

• Listening port of the Oracle listener.

• Name of the Oracle Instance (SID) or Service Name

• Login and password of an Oracle User.

An Oracle data server corresponds to an Oracle Database Instance connected with a specific Oracle user account. This user will have access to several schemas in this instance, corresponding to the physical schemas in Oracle Data Integrator created under the data server.

Create an Oracle physical schema using the standard procedure, as described in Creating a Physical Schema in Administering Oracle Data Integrator.

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.

Create an Oracle Model using the standard procedure, as described in Creating a Model of Developing Integration Projects with Oracle Data Integrator.

Oracle supports both Standard reverse-engineering - which uses only the abilities of the JDBC driver - and Customized reverse-engineering, which uses a RKM to retrieve the structure of the objects directly from the Oracle dictionary.

In most of the cases, consider using the standard JDBC reverse engineering for starting. Standard reverse-engineering with Oracle retrieves tables, views, columns, primary keys, and references.

Consider switching to customized reverse-engineering for retrieving more metadata. Oracle customized reverse-engineering retrieves the table and view structures, including columns, primary keys, alternate keys, indexes, check constraints, synonyms, and references.

Standard Reverse-Engineering

To perform a Standard Reverse-Engineering on Oracle use the usual procedure, as described in Reverse-engineering a Model of Developing Integration Projects with Oracle Data Integrator.

Customized Reverse-Engineering

To perform a Customized Reverse-Engineering on Oracle with a RKM, use the usual procedure, as described in Reverse-engineering a Model of Developing Integration Projects with Oracle Data Integrator. This section details only the fields specific to the Oracle technology:

In the Reverse Engineer tab of the Oracle Model, select the KM: RKM Oracle.<project name>.

Oracle can be used as a source, target or staging area of a mapping. The LKM choice in the Mapping's Loading Knowledge Module tab to load data between Oracle and another type of data server is essential for the performance of a mapping.

The data integration strategies in Oracle are numerous and cover several modes. The IKM choice in the Mapping's Physical diagram determines the performances and possibilities for integrating.

The following KMs implement optimized methods for integrating data into an Oracle target. In addition to these KMs, you can also use the Generic SQL KMs.

Using Slowly Changing Dimensions

For using slowly changing dimensions, make sure to set the Slowly Changing Dimension value for each column of the Target datastore. This value is used by the IKM Oracle Slowly Changing Dimension to identify the Surrogate Key, Natural Key, Overwrite or Insert Column, Current Record Flag and Start/End Timestamps columns.

Using Multi Table Insert

The IKM Oracle Multi Table Insert is used to integrate data from one source into one to many Oracle target tables with a multi-table insert statement. This IKM must be used in mappings that are sequenced in a Package. This Package must meet the following conditions:

• The first mapping of the Package must have a temporary target and the KM option DEFINE_QUERY set to YES.

  This first mapping defines the structure of the SELECT clause of the multi-table insert statement (that is the source flow).

• Subsequent mappings must source from this temporary datastore and have the KM option IS_TARGET_TABLE set to YES.

• The last mapping of the Package must have the KM option EXECUTE set to YES in order to run the multi-table insert statement.

• Do not set Use Temporary Mapping as Derived Table (Sub-Select) to true on any of the mappings.

If large amounts of data are appended, consider to set the KM option OPTIMIZER_HINT to /*+ APPEND */.

Using Spatial Datatypes

To perform incremental update operations on Oracle Spatial datatypes, you need to declare the SDO_GEOMETRY datatype in the Topology and use the IKM Oracle Spatial Incremental Update. When comparing two columns of SDO_GEOMETREY datatype, the GEOMETRY_TOLERANCE option is used to define the error margin inside which the geometries are considered to be equal. See the Oracle Spatial User's Guide and Reference, for more information.

See Creating a Mapping in Developing Integration Projects with Oracle Data Integrator for generic information on how to design mappings. This section describes how to design an ETL-style mapping where the staging area is Oracle database or any ANSI-92 compliant database and the target on Oracle database.

In an ETL-style mapping, ODI processes the data in a staging area, which is different from the target. Oracle Data Integrator provides two ways for loading the data from an Oracle staging area to an Oracle target:

• Using a Multi-connection IKM

• Using an LKM and a mono-connection IKM

Depending on the KM strategy that is used, flow and static control are supported.

Using a Multi-connection IKM

A multi-connection IKM allows updating a target where the staging area and sources are on different data servers.

Oracle Data Integrator provides the following multi-connection IKM for handling Oracle data: IKM Oracle to Oracle Control Append (DBLINK). You can also use the generic SQL multi-connection IKMs. See Generic SQL for more information.

See Table 2-5 for more information on when to use a multi-connection IKM.

To use a multi-connection IKM in an ETL-style mapping:

1. Create a mapping with the staging area on Oracle or an ANSI-92 compliant technology and the target on Oracle using the standard procedure as described in Creating a Mapping of Developing Integration Projects with Oracle Data Integrator. This section describes only the ETL-style specific steps.

2. Change the staging area for the mapping to the logical schema of the source tables or a third schema. See Configuring Execution Locations of Developing Integration Projects with Oracle Data Integrator for information about how to change the staging area.

3. In the Physical diagram, select an access point. The Property Inspector opens for this object.

4. In the Loading Knowledge Module tab, select an LKM to load from the source(s) to the staging area. See Table 2-5 to determine the LKM you can use.

5. Optionally, modify the KM options.

6. In the Physical diagram, select the Target by clicking its title. The Property Inspector opens for this object.

   In the Integration Knowledge Module tab, select an ETL multi-connection IKM to load the data from the staging area to the target. See Table 2-5 to determine the IKM you can use.

Note the following when setting the KM options:

• For IKM Oracle to Oracle Control Append (DBLINK)

  • If large amounts of data are appended, set the KM option OPTIMIZER_HINT to /*+ APPEND */.

  • Set AUTO_CREATE_DB_LINK to true to create automatically db link on the target schema. If AUTO_CREATE_DB_LINK is set to false (default), the link with this name should exist in the target schema.

  • If you set the options FLOW_CONTROL and STATIC_CONTROL to Yes, select a CKM in the Check Knowledge Module tab. If FLOW_CONTROL is set to Yes, the flow table is created on the target.

Using an LKM and a mono-connection IKM

If there is no dedicated multi-connection IKM, use a standard exporting LKM in combination with a standard mono-connection IKM. The exporting LKM is used to load the flow table from the staging area to the target. The mono-connection IKM is used to integrate the data flow into the target table.

Oracle Data Integrator supports any ANSI SQL-92 standard compliant technology as a source of an ETL-style mapping. Staging area and the target are Oracle.

See Table 2-5 for more information on when to use the combination of a standard exporting LKM and a mono-connection IKM.

To use an LKM and a mono-connection IKM in an ETL-style mapping:

Errors appear often in Oracle Data Integrator in the following way:

java.sql.SQLException: ORA-01017: invalid username/password; logon denied
at ...
at ...
...

the java.sql.SQLExceptioncode simply indicates that a query was made to the database through the JDBC driver, which has returned an error. This error is frequently a database or driver error, and must be interpreted in this direction.

Only the part of text in bold must first be taken in account. It must be searched in the Oracle documentation. If its contains an error code specific to Oracle, like here (in red), the error can be immediately identified.

If such an error is identified in the execution log, it is necessary to analyze the SQL code send to the database to find the source of the error. The code is displayed in the description tab of the erroneous task.

This section describes common problems and solutions.

• ORA-12154 TNS:could not resolve service name

  TNS alias resolution. This problem may occur when using the OCI driver, or a KM using database links. Check the configuration of the TNS aliases on the machines.

• ORA-02019 connection description for remote database not found

  You use a KM using non existing database links. Check the KM options for creating the database links.

• ORA-00900 invalid SQL statement

  ORA-00923 FROM Keyword not found where expected

  The code generated by the mapping, or typed in a procedure is invalid for Oracle. This is usually related to an input error in the mapping, filter of join. The typical case is a missing quote or an unclosed bracket.

  A frequent cause is also the call made to a non SQL syntax, like the call to an Oracle stored procedure using the syntax

  EXECUTE SCHEMA.PACKAGE.PROC(PARAM1, PARAM2).
  

  The valid SQL call for a stored procedure is:

  BEGIN
  SCHEMA.PACKAGE.PROC(PARAM1, PARAM2);
  END;
  

  The syntax EXECUTE SCHEMA.PACKAGE.PROC(PARAM1, PARAM2) is specific to SQL*PLUS, and do not work with JDBC.

• ORA-00904 invalid column name

  Keying error in a mapping/join/filter. A string which is not a column name is interpreted as a column name, or a column name is misspelled.

  This error may also appear when accessing an error table associated to a datastore with a recently modified structure. It is necessary to impact in the error table the modification, or drop the error tables and let Oracle Data Integrator recreate it in the next execution.

• ORA-00903 invalid table name

  The table used (source or target) does not exist in the Oracle schema. Check the mapping logical/physical schema for the context, and check that the table physically exists on the schema accessed for this context.

• ORA-00972 Identifier is too Long

  There is a limit in the object identifier in Oracle (usually 30 characters). When going over this limit, this error appears. A table created during the execution of the mapping went over this limit. and caused this error (see the execution log for more details).

  Check in the topology for the oracle technology, that the maximum lengths for the object names (tables and columns) correspond to your Oracle configuration.

• ORA-01790 expression must have same datatype as corresponding expression

  You are trying to connect two different values that can not be implicitly converted (in a mapping, a join...). Use the explicit conversion functions on these values.