Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Sql Loader - Problem while loading french data
Greetings,
We are involved in migration of data from DB2 to Oracle. We are facing some problem while loading some french data to oracle table's using sql loader.
Table (tb_test) structure:
desc TB_TEST
Name Null
------------------------------ -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- COL1 NOT NULL
load data
CHARACTERSET WE8MSWIN1252
infile 'test1.dat'
into table TB_TEST
REPLACE
fields terminated by '~' optionally enclosed by '"' trailing NULLCOLS
(
COL1 "TRANSLATE(COL1,'0123456789,','0123456789')"
,COL2
,COL3
)
Data file (test.dat) contents:
+11370.~Universityâs something Graduate School of Management~
+11710.~Faculdade Nacional de Ciências Econômicas da Universidade do
Japan~
+13771.~Trium (University â London School â HEC Paris)~
The above data is just a subset of a parent flat file. Actually these are the bad records which I get when loading parent flat file.
Problem here is when I try to load the whole parent file using sql
loader,
I get the following error:
Record 47243: Rejected - Error on table TB_TEST, column COL2.
ORA-12899: value too large for column
"SCHEMANAME"."TB_TEST"."COL2" (actual: 78, maximum: 75)
Out of the three records first two gets loaded and the last records gets rejected.
DB information:
SQL> select * from v$version;
BANNER
SELECT * FROM NLS_DATABASE_PARAMETERS
PARAMETER VALUE ------------------------------ ------------------------------ NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET AL32UTF8 NLS_LENGTH_SEMANTICS BYTE
Also, if I try to load these 3 records using sql loader separately
into a
temp table with same structure as TB_TEST, it loads without any
problems.
I am not able to figure out what is the exact problem here. Do i have to take into some special consideration while loading data other than english?
Any help would be appreciated.
TIA Received on Thu Aug 16 2007 - 06:30:11 CDT