OutputOracleTransformer

Overview

Transforms DataStage Oracle OUTPUT stages to SQL passthrough SELECT statements. Output stages are treated as data selection only (no DML operations generated).

Stage Type: OUTPUT (pxbridge operator with OracleConnectorPX) Output: SQL SELECT passthrough

Important Note

OUTPUT stages do NOT generate INSERT/UPDATE/DELETE SQL. The converter only handles data transformation (SELECT statements). Actual data loading must be configured separately in Snowflake.

Capabilities

• Passthrough all columns from input (SELECT *)
• Passthrough specific columns only
• Preserves input schema
• Documents target table metadata

DataStage Stage Example

Input (.dsx format)

#### STAGE: OutputTargetTable
## Operator
pxbridge
## Operator options
-Orientation link
-XMLProperties '<?xml version=\'1.0\' encoding=\'UTF-16\'?>
<Properties version=\'1.1\'>
  <Connection>
    <Server>[&"instance"]</Server>
    <Username>[&"username"]</Username>
  </Connection>
  <Usage>
    <TableName>SCHEMA.TARGET_TABLE</TableName>
    <BeforeSQL>DELETE SCHEMA.TARGET_TABLE WHERE ...</BeforeSQL>
  </Usage>
</Properties>'
-connector '{name=OracleConnector}'
-target 0 '{
      DSSchema=\'record (
        EVENT_DATE:nullable timestamp;
        SCENARIO_CODE:nullable string[max=10];
        VERSION_CODE:nullable string[max=50];
        SOURCE_INFO:nullable string[max=100];
        LOAD_DATE:nullable timestamp;
      )\'
}'

## General options
[ident('OutputTargetTable'); jobmon_ident('OutputTargetTable')]
## Inputs
0< [] 'ProcessedData:output.v'

Generated SQL Output

SELECT
  *
FROM processeddata_V0S18

Output Schema

[
  {"name": "EVENT_DATE", "type": "timestamp"},
  {"name": "SCENARIO_CODE", "type": "string"},
  {"name": "VERSION_CODE", "type": "string"},
  {"name": "SOURCE_INFO", "type": "string"},
  {"name": "LOAD_DATE", "type": "timestamp"}
]

How It Works

1. Identify Output Stage

Recognizes Oracle Connector output stages:

## Operator
pxbridge
-connector '{name=OracleConnector}'

2. Extract Metadata

Documents (but does not convert):

• TableName: Target table name
• BeforeSQL: Pre-load SQL statements
• AfterSQL: Post-load SQL statements

3. Generate Passthrough SELECT

Creates SELECT from upstream stage:

• With columns defined: SELECT with explicit column list
• Without columns: SELECT * (full passthrough)

SELECT
  <columns or *>
FROM upstream_stage

BeforeSQL and AfterSQL

DataStage OUTPUT stages can define SQL to run before/after data loading. These are documented as metadata but NOT converted to SQL. You must recreate them manually in Snowflake using stored procedures or orchestration.

Limitations

• ⚠️ No DML generation - INSERT/UPDATE/DELETE not generated by converter
• ⚠️ BeforeSQL/AfterSQL not converted - Must be manually recreated in Snowflake

Related Transformers

• ModifyStageTransformer - For column filtering before output
• TransformerStageTransformer - For column derivations before output
• InputStageTransformer - Opposite operation (reads from Oracle)