Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Sql Loader - Problem while loading french data

Sql Loader - Problem while loading french data

From: <pankaj_wolfhunter_at_yahoo.co.in>
Date: Thu, 16 Aug 2007 04:27:49 -0700
Message-ID: <1187263669.500724.196980@g4g2000hsf.googlegroups.com>


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

Type
------------------------------ --------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COL1                        NOT NULL

NUMBER(10)
COL2
VARCHAR2(75)
COL3
VARCHAR2(50) Control File (test.ctl) Structure:

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



Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi PL/SQL Release 10.2.0.3.0 - Production

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:27:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US