This discussion is archived
1 Reply Latest reply: Nov 15, 2012 6:11 AM by ReneW. RSS

External table fixed record length and UTF8

ReneW. Explorer
Currently Being Moderated
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.?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points