On Aug 16, 6:58 pm, "Vladimir M. Zakharychev"
<vladimir.zakharyc..._at_gmail.com> wrote:
> On Aug 16, 3:30 pm, "pankaj_wolfhun..._at_yahoo.co.in"
>
>
>
>
>
> <pankaj_wolfhun..._at_yahoo.co.in> wrote:
> > 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
>
> I think the problem is with the column length semantics: since
> VARCHAR2 uses BYTE length semantics by default in your database
> (NLS_LENGTH_SEMANTICS=BYTE), VARCHAR2(75) is 75 bytes. But the
> database charset is AL32UTF8, in which code points can occupy from 1
> to 4 bytes. Some French characters in the input are translated into
> multibyte code points and resulting string length in bytes may exceed
> 75 even though it may be less than 75 bytes in windows-1252 charset.
> Try defining COL2 as VARCHAR2(75 CHAR) and see if the problem will
> persist.
>
> Hth,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com- Hide quoted text -
>
> - Show quoted text -
Thanks a zillion Vladimir. That worked.
One more doubt, can be specify this at control file level.
something like column_name VARCHAR2(75 CHAR)?
Received on Thu Aug 16 2007 - 10:45:02 CDT
