Re: Sql Loader - Problem while loading french data

On Aug 16, 7:45 pm, "pankaj_wolfhun..._at_yahoo.co.in" <pankaj_wolfhun..._at_yahoo.co.in> wrote:
> 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
>
> Thanks a zillion Vladimir. That worked.
> One more doubt, can be specify this at control file level.
> something like column_name VARCHAR2(75 CHAR)?

No, that must be specified at the database level as it's table column property. Actually, you should always keep length semantics in mind: for example, SUBSTR() function always uses char length semantics, and with multi-byte character sets and byte length semantics for columns this might lead to unexpected results, like SUBSTR(string,1,75) not fitting a VARCHAR2(75) column. You either need to use SUBSTRB() or explicitly define columns using char length semantics. For more information on this refer to SQL Reference, description of VARCHAR2 data type.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Thu Aug 16 2007 - 15:03:30 CDT