Oracle Enterprise stage

The Oracle Enterprise stage is a database stage that you can use to read data from and write data to an Oracle database. It can also be used in conjunction with a Lookup stage to access a lookup table that is hosted by an Oracle database.

When you use IBM® InfoSphere® DataStage® to access Oracle databases, you can choose from a collection of connectivity options. For most new jobs, use the Oracle Connector stage, which offers better functionality and performance than the Oracle Enterprise stage.

If you have jobs that use the Oracle Enterprise stage and want to use the connector, use the Connector Migration Tool to migrate jobs to use the connector.

The Oracle Enterprise stage can have a single input link and a single reject link, or a single output link or output reference link.

The stage performs one of the following operations:

Updates an Oracle table using INSERT or UPDATE or both as appropriate. Data is assembled into arrays and written using Oracle host-array processing.
Loads an Oracle table (by using Oracle fast loader).
Reads an Oracle table.
Deletes rows from an Oracle table.
Performs a lookup directly on an Oracle table.
Loads an Oracle table into memory and then performs a lookup on it.

When you use an Oracle stage as a source for lookup data, there are special considerations about column naming. If you have columns of the same name in both the source and lookup data sets, note that the source data set column will go to the output data. If you want this column to be replaced by the column from the lookup data source, you need to drop the source data column before you perform the lookup (you can, for example, use a Modify stage to do this).

For more information about performing lookups, see the topics about the Lookup stage.

When you edit a Oracle Enterprise stage, the Oracle Enterprise stage editor appears.

The stage editor has up to three pages, depending on whether you are reading or writing a database:

Stage Page. This is always present and is used to specify general information about the stage.
Inputs Page. This is present when you are writing to a Oracle database. This is where you specify details about the data being written.
Outputs Page. This is present when you are reading from a Oracle database, or performing a lookup on an Oracle database. This is where you specify details about the data being read.

Note: For Oracle direct path load, the client version must be the same as or earlier than the server version. You should have read and execute permissions to use libraries in the $ORACLE_HOME/lib and $ORACLE_HOME/bin directories and read permissions on all files in the $ORACLE_HOME directory. Otherwise, you might experience problems using Oracle enterprise stage to connect to Oracle.