0 Replies Latest reply: Nov 19, 2012 4:17 AM by 902023 RSS

    Unicode File not returning expected fields

    902023
      Hi,

      We are running Oracle 11.2.0.1.0 in Windows 2008 R2.

      I need to read / load an UNICODE text file - using an External Table. For some reason the table is omitting the null columns and rearranging the columns - placing all the data without nulls next to each other, but keeping the column names in the same order. The Unicode delimeted text file is used because of the multibyte characters that we need to load (Mandrin to be specific).

      The control file looks like this:
      CREATE TABLE EIGER_STG.HM_CUSTOMERS_CNCAR_E_UNI
      (
      COMPANY_ID NVARCHAR2(512),
      CUSTOMER_NUMBER NVARCHAR2(512),
      SHORT_NAME NVARCHAR2(512),
      STATUS NVARCHAR2(512),
      DESC_STATUS NVARCHAR2(512),
      CURRENCY NVARCHAR2(512),
      LANGUAGE NVARCHAR2(512),
      DESC_LANGUAGE NVARCHAR2(512)
      )
      ORGANIZATION EXTERNAL
      ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY FILE_LOCATION_CNCAR_FOR_JDE
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
      CHARACTERSET UTF16
      DATA IS LITTLE ENDIAN
      BYTEORDERMARK CHECK
      STRING SIZES ARE IN CHARACTERS
      BADFILE FILE_LOCATION_CNCAR_FOR_JDE: 'BAD_CUSTOMERS_CNCAR_E_UNI.txt'
      NODISCARDFILE
      LOGFILE FILE_LOCATION_CNCAR_FOR_JDE: 'LOG_CUSTOMERS_CNCAR_E_UNI.txt'
      FIELDS
      TERMINATED BY '\t'
      OPTIONALLY ENCLOSED BY '"' AND '"'
      MISSING FIELD VALUES ARE NULL
      (
      "COMPANY_ID" CHAR(255) DEFAULTIF "COMPANY_ID"=BLANKS,
      "CUSTOMER_NUMBER" CHAR(255) DEFAULTIF "CUSTOMER_NUMBER"=BLANKS,
      "SHORT_NAME" CHAR(255) DEFAULTIF "SHORT_NAME"=BLANKS,
      "STATUS" CHAR(255) DEFAULTIF "STATUS"=BLANKS,
      "DESC_STATUS" CHAR(255) DEFAULTIF "DESC_STATUS"=BLANKS,
      "CURRENCY" CHAR(255) DEFAULTIF "CURRENCY"=BLANKS,
      "LANGUAGE" CHAR(255) NULLIF "LANGUAGE"=BLANKS , -- ... DEFAULTIF here gives the same result...
      "DESC_LANGUAGE" CHAR(255) DEFAULTIF "DESC_LANGUAGE"=BLANKS
      )
      )
      LOCATION (FILE_LOCATION_CNCAR_FOR_JDE:'CUSTOMERS_CNCAR_E.txt')
      )
      REJECT LIMIT UNLIMITED
      NOPARALLEL

      The query restuns:
      Company ID     Customer Number     Short Name     Status     Desc Status     Currency     Language     Desc Language
      88     10980005     Baker Hughes Incorporated(USA)     2     ina     USD     A     KG01
      88     10980006     Brunner & Lay Inc.(USA)     2     ina     USD     KG01     End Users
      88     10980009     Delta-Building(RUSSIA)     2     ina     EUR     KG01     End Users

      The last two columns should be blank / NULL...but it contains data from the next column in the file that are not null.

      The PRESERVE BLANKS option causes and error:
      KUP-01005: syntax error: found "identifier": expecting one of: "comma, date_format, defaultif, enclosed, ltrim, lrtrim, ldrtrim, notrim, nullif, optionally, ), rtrim, terminated"
      KUP-01008: the bad identifier was: PRESERVE

      Could someone please tell me what is wrong with the control file?

      Thanks,
      Regards
      Steven