Re: Sql Loader - Problem while loading french data

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 Received on Thu Aug 16 2007 - 08:58:22 CDT