DSXchange

Hi Friends,

We are trying to load some large tables for Sybase IQ to Oracle via Oracle OCI Bulk stage. Some of the columns contain the characters | & " and that is causing the bulk loader to return errors given below.

1. Record 14294: Rejected - Error on table DW.D_VENDOR, column ENABLED_FLAG.
ORA-01722: invalid number
2. Record 11048: Rejected - Error on table DW.D_VENDOR, column VENDOR_NAME.
no terminator found after TERMINATED and ENCLOSED field

Any thoughts or suggestions to overcome this?

Cheers!

Since numeric columns cannot contain those special characters, ensure that they aren't put in there - several DataStage methods exist including string replacement and numeric conversion functions. For error number (2) please post line 11048 to see exactly what error is in the data.

Set the NLS_LANG parameter and specify the proper character set as AMERICAN_AMERICA.AL32UTF8

For the error number two it contains the | symbol in the data itself. And the field delimiter used by the bulk loader dat file is also the same.

I think sqlldr will use normal escaping methods, so try replacing all "|" in your data with "\|" to get rod of error #2

Pretty normal stuff, make sure your numbers are numbers and your delimiter doesn't occur in your data regardless of what it is.