1 Reply Latest reply: Nov 15, 2012 8:11 AM by Rene W. RSS

    External table fixed record length and UTF8

    Rene W.
      I'm trying to read a UTF8 file that contains a stream of characters. It contains records of 256 characters long. Character ö (C3 B6) takes two bytes and the record is 257 bytes long. This does not go well with my external table definition. The records gets shifted by 1 byte.

      This thread discusses the exact same problem: Re: External Table - Issues With Special Character.
      but unfortunately there is no answer there.
      -- Create table
      create table "Stuf.Tax4"
      (
        tabelregel VARCHAR2(256 CHAR)
      )
      organization external
      (
        type ORACLE_LOADER
        default directory ODS_SERVER_DIRECTORY
        access parameters 
        (
          RECORDS FIXED 256 CHARACTERSET UTF8 STRING SIZES ARE IN CHARACTERS
          NOBADFILE
          NODISCARDFILE
          NOLOGFILE
          READSIZE 5242880
        )
        location (ODS_SERVER_DIRECTORY:'Stuf.Tax4')
      )
      reject limit UNLIMITED;
      According to the documentation: The FIXED clause is used to identify the records as all having a fixed size of length bytes
      (http://docs.oracle.com/cd/B14117_01/server.101/b10825/et_params.htm#i1009499)

      There is no "end of record" character so I have to go by length.

      Is there a way to read this kind of files using external tables or do I have to read it as CLOB and get the records out using substr instr etc.?