1 Reply Latest reply on Jun 4, 2013 3:00 PM by Zoltan Kecskemethy

    SQL Loader loads special character incorrectly

      Hi All-

      We are trying to load some xml files via
      sqlldr user/pswd@xe control='C:\xmlldr.ctl' data='listoffiles.dat'

      where xmlldr.ctl is the following:

      load data
      into table LOADER_CTG_PROTOCOL

      xmlfile lobfile(nct_code) terminated by eof,
      nct_code char(15) "substr(:nct_code,1,11)",
      created_dt sysdate

      I was playing with different CHARACTERSET, but some special characters e.g. "greater than or equal" do not get loaded/displayed correctly in the database..
      Also tried changing NLS_LANG registry key and following some advices in the Oracle doc https://support.oracle.com/epmos/faces/DocumentDisplay?id=788156.1 but no luck. Any ideas. Thank you in advance for any help.
        • 1. Re: SQL Loader loads special character incorrectly
          Zoltan Kecskemethy
          As I see you have MOS access I suggest to check:
          [url https://support.oracle.com/epmos/faces/DocContentDisplay?id=257909.1]SQL*Loader And NLS ID 257909.1

          See more info how to setup NLS_LANG for you env:
          [url https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=179133.1 ]MOS: The correct NLS_LANG in a Windows Environment Doc ID: 179133.1
          [url https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=264157.1]MOS: The correct NLS_LANG setting in Unix Environments Doc ID: 264157.1

          And I would convert my input file to UTF8 encoded if your database has an UTF character set so you could leave UTF8 at NLS_LANG as well.

          HTH, Zoltan

          Edited by: Zoltan Kecskemethy on Jun 4, 2013 4:59 PM