UTF-8 Character Processing in Oracle External Tables

Question

I have an external table that is read from a file with a fixed length. The file is expected to contain special characters. In my case, the word containing the special character is Gothenburg. Since "ö" is a special character, it looks like Oracle treats it as 2 bytes. This is causing problems. Subsequent fields in the files are shifted by 1 byte, thereby corrupting the data. Has anyone encountered a problem before. So far, we have tried the following solution:

NLS_LANG value changed for AMERICAN_AMERICA.WE8ISO8859P1
Tried setting database character in UTF-8
Tried changing NLS_LENGTH_SYMMANTIC to CHAR instead of BYTE using ALTER SYSTEM
I tried changing the external character set of the table: AL32UTF8
I tried changing the appearance of the External table to: UTF-8

Nothing works. Other details include:

• File encoded in UTF-8 encoding
• Operating System: RHEL
• Database: Oracle 11g

Anything else I might be missing? Any help would be appreciated. Thanks!

Answer 1

nls_length_semantics refers only to the creation of new tables.

Below I tried to solve this problem.

records delimited by newline CHARACTERSET AL32UTF8 STRING SIZES ARE IN CHARACTERS 

i.e.

 ALTER SESSION SET nls_length_semantics = CHAR / CREATE TABLE TDW_OWNER.SDP_TST_EXT ( COST_CENTER_CODE VARCHAR2(10) NULL, COST_CENTER_DESC VARCHAR2(40) NULL, SOURCE_CLIENT VARCHAR2(3) NULL, NAME1 VARCHAR2(35) NULL ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY DBA_DATA_DIR ACCESS PARAMETERS ( records delimited by newline CHARACTERSET AL32UTF8 STRING SIZES ARE IN CHARACTERS logfile DBA_DATA_DIR:'sdp_tst_ext_%p.log' badfile DBA_DATA_DIR:'sdp_tst_ext_%p.bad' discardfile DBA_DATA_DIR:'sdp_tst_ext_%p.dsc' fields notrim ( COST_CENTER_CODE CHAR(10) ,COST_CENTER_DESC CHAR(40) ,SOURCE_CLIENT CHAR(3) ,NAME1 CHAR(35) ) ) LOCATION (DBA_DATA_DIR:'sdp_tst.dat') ) REJECT LIMIT UNLIMITED NOPARALLEL NOROWDEPENDENCIES /