problems loading multilingual data using SQL*Loader with External Tables ...

Hello,

(Oracle 9i patchset level 9.2.0.5 on Windows XP)

I have been trying to insert hebrew data through Oracle SQL*Loader using External Tables and all records get discarded as bad even though the data delimiters and definition match exactly the external table definition (which works fine in production with english/european-based languages). One of the external tables that get all data discarded by SQL*Loader
looks like this:

• ============================================================
• Table: EXT_NLM_ENTITY
• ============================================================ create table EXT_NLM_ENTITY
  (
  --// columns here ) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY MY_EXT_DIR ACCESS PARAMETERS
  (
  RECORDS DELIMITED BY NEWLINE BADFILE MY_EXT_DIR:'EXT_NLM_ENTITY.BAD' NOLOGFILE FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' MISSING FIELD VALUES ARE NULL ) LOCATION ('EXT_NLM_ENTITY.DAT')) PARALLEL REJECT LIMIT UNLIMITED /

Since there was no way to make it work with an instance configured with the default character set WE8MSWIN1252 I have tried doing the following:

• Installed the Hebrew locale settings into my Windows XP.
• Installed a new Oracle instance with character set unicode "AL32UTF8" specified the following registry: nls_language=HEBREW nls_territory=ISRAEL
• Converted my external files to unicode UTF-8 using conversion option from UltraEdit v11.x

Tried executing the loading process and again got all my records discarded as bad. Looked into the alert_XXX.log and tracing files and no information about it ...

Has anyone being through this same problem before? does anyone know how to recover error messages provided from failing records when using SQL*Loader?

Many thanks in advance,
Best Regards,
Giovanni Received on Thu Apr 28 2005 - 04:40:58 CDT