Information icon IBM InfoSphere Information Server, Version 8.5
space Feedback

Updating an Oracle table

This example shows an Oracle table being updated with three new columns. The database records the horse health records of a large stud. Details of the worming records are being added to the main table and populated with the most recent data, using the existing column "name" as a key. The metadata for the new columns is as follows:

Table 1. Column metadata on the Properties tab
Column name Key SQL type Extended Length Scale Nullable
name Yes Char       No
wormer_type   Char Unicode     No
dose_interval   Char Unicode     No
dose_level   Char Unicode     No

Specify upsert as the write method and select User-defined Update & Insert as the upsert mode. The existing name column is not included in the INSERT statement. The properties (showing the INSERT statement) are shown below. The INSERT statement is as generated by the IBM® InfoSphere™ DataStage®, except the name column is removed.

INSERT
INTO
horse_health
(wormer_type, dose_interval, dose_level)
VALUES
(ORCHESTRATE.name,
ORCHESTRATE.wormer_type,
ORCHESTRATE.dose_interval,
ORCHESTRATE.dose_level)

The UPDATE statement is as automatically generated by the InfoSphere DataStage:

UPDATE
horse_health
SET
wormer_type=ORCHESTRATE.wormer_type,
dose_interval=ORCHESTRATE.dose_interval,
dose_level=ORCHESTRATE.dose_level
WHERE
(name=ORCHESTRATE.name)

PDFThis topic is also in the IBM InfoSphere DataStage and QualityStage Connectivity Guide for Oracle Databases.

Update timestamp Last updated: 2011-3-2