5

When trying to load an UTF-8 file with ¥ separator, it doesn't separate the fields when it finds the character. We get an ORA-12899 error from the value exceeding the column size, since it tries to put the entire line into the first column.

In UTF-8 , ¥ is Â¥ ( C2A5), but it seems that it is trying to find C2C2A5.

The database settings are

NLS_LANGUAGE    SPANISH
NLS_TERRITORY   SPAIN
NLS_CURRENCY    ?
NLS_ISO_CURRENCY    SPAIN
NLS_NUMERIC_CHARACTERS  ,.
NLS_CHARACTERSET    WE8ISO8859P15
NLS_CALENDAR    GREGORIAN
NLS_DATE_FORMAT DD/MM/RR
NLS_DATE_LANGUAGE   SPANISH
NLS_SORT    SPANISH
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT    DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT  HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY   ?
NLS_COMP    BINARY
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_RDBMS_VERSION   11.2.0.3.0

We have tried with:

NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
NLS_LANG=SPANISH_SPAIN.WE8MSWIN1252

We are running SQL*Loader from a command window on Windows 7 machine with Spanish locale.

Control file:

LOAD DATA
CHARACTERSET UTF8
INFILE  'C:\tmp\xxxy.txt'
BADFILE 'C:\tmp\xxxy.bad'
TRUNCATE INTO TABLE  SAMPLE.TEST_CAR_VENCIMI_INTERMED
fields terminated by "¥"
TRAILING NULLCOLS
(
CODE             CHAR "upper(ltrim(rtrim(:CODE)))",
CIF              CHAR "upper(ltrim(rtrim(:CIF)))",
CODIGO_PRESTAMO  CHAR "upper(ltrim(rtrim(:CODIGO_PRESTAMO)))"
)

Sample data file:

21AK0100¥P11111111F¥910002¥000000000106013.64¥666666666¥mail@domain.com¥address¥11111¥Name¥Surname 1¥Surname 2¥mail2@domain.com¥666666666
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
luis
  • 53
  • 4
  • As a starting point, on the assumption it's confused somewhere down the line, what are your database character set, NLS_LANG setting, and OS characterset? Can you add an example control file and dummy data that demonstrates the problem? Or if the file can be put on the server, can you see if it's accepted using an external table instead of SQL\*Loader? – Alex Poole Jun 03 '15 at 09:37
  • Please add that info to the question as an edit to it can be formatted to be readable, not as a comment. You need the operating system charaterset too, and your NLS_LANG environment variable. (Or the nls_session_parameters from SQL\*Plus running on the same account you're running SQL\*Loader from, but NLS_LANG is shorter) – Alex Poole Jun 05 '15 at 09:50
  • Thank you @AlexPoole, i have updated the question – luis Jun 05 '15 at 10:06
  • Can you try setting NLS_LANG to `"SPANISH_SPAIN.UTF8"`? That works for me, but I'm running under Linux so I can't verify it'll work for your Windows environment... – Alex Poole Jun 05 '15 at 10:57
  • Thank you so much. It works! – luis Jun 05 '15 at 11:24

1 Answers1

1

Since you're using a UTF8 character and a UTF8 file format (I think), for the session that runs SQL*Loader set your NLS_LANG environment variable to "SPANISH_SPAIN.UTF8".

Alex Poole
  • 183,384
  • 11
  • 179
  • 318