I'm having an issue with external tables, that I don't understand. Hoping somebody can help me out?
Here is how I create the external table.
CREATE TABLE TBLTMP_MES1_MATERI_MASTER_DATA
( "MARC_WERKS" VARCHAR2(4 CHAR),
"MARA_MATNR" VARCHAR2(18 CHAR),
"MARA_MAKTX" VARCHAR2(40 CHAR),
"MARC_ZIV_PRDSTOP" VARCHAR2(1 CHAR)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "ENDERECO_REDE_INBOUND"
ACCESS PARAMETERS
( records delimited by '\n'
CHARACTERSET UTF8
NOLOGFILE
FIELDS LRTRIM
MISSING FIELD VALUES ARE NULL (
MARC_WERKS POSITION (1:4) CHAR,
MARA_MATNR POSITION (5:22) CHAR,
MARA_MAKTX POSITION (28:67) CHAR,
MARC_ZIV_PRDSTOP POSITION (357:357) CHAR
)
)
LOCATION
( "ENDERECO_REDE_INBOUND":'MES1.txt'
)
)
REJECT LIMIT UNLIMITED;
The file is a utf8 without BOM (verified with a Hex Editor), the word 'JUN¸AO' in Hex editor is '4A 55 4E C2 B8 41 4F'.
The external table write in the .bad file all lines that have words with accent like ã, ç, í and so on...
I already create a file with different charset like ISO-8859-1 and changed the charset of the create table script to WE8ISO8859P1 and works.
Are there some problem with loading UTF8 file?
Could someone explain what I missing?
Thanks in advance.