Oracle temporary table fields are not big enough

[dvdrndlph]

Issue Description

Running with sqlldr, both incremental and truncate loads fail with this error:

Record 86: Rejected - Error on table "CABOODLE"."MEDICATIONORDERFACT_TMP3G", column "OrderSummary_X".
ORA-12899: value too large for column OrderSummary_X (actual: 201, maximum: 200)

The source table type in SQL Server is nvarchar(200).The temporary table field is varchar2(200 BYTE).

I think the problem is the source field can have hold as much as 400 bytes (200 2-byte characters). There is only a problem when a two-byte character appears in a full source field. Then Oracle cannot accommodate the data.

• Sling version (sling --version):
  1.4.3

• Operating System (linux, mac, windows):
  Windows

• Replication Configuration:

defaults:
  mode: incremental
  object: '{target_schema}.{stream_table}'
  update_key: _LastUpdatedInstant

streams:
  MedicationOrderFact:
    primary_key: MedicationOrderKey
    # mode: truncate

• Log Output (please run command with -d):

The error is captured in the sqlldr log, as captured above.

[dvdrndlph]

I have worked around this by specifying the width of the field like so:

  MedicationOrderFact:
    primary_key: MedicationOrderKey
    columns:
      OrderSummary_X: string(400)

But I really think this issue should stay open. Sling should provide a general mechanism for overriding the data-type crosswalk for an entire replication.

[flarco]

Hey @dvdrndlph, this one is tricky, as various databases have different encoding/length behaviors. At some point, sling used varchar(max) / clob like DDL type for every string columns, but this ended-up with the issue of possible poor performance / large storage. This was modified and logic was added to have more precise varchar lengths, which cause issues like this.

Could you elaborate on what you mean by this? Sling should provide a general mechanism for overriding the data-type crosswalk for an entire replication.