Loading data with special characters from Oracle Bulk Loader

[chanaka]

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!

[ArndW]

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.

[dr.murthy]

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

[chanaka]

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.

[ArndW]

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

[chulett]

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