Re: Sql Loader - Problem while loading french data

On Aug 17, 1:03 am, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Thanks Vladimir. Got one more issue.

While loading data using sql-loader I got the following error:

SQL*Loader-951: Error calling once/load initialization ORA-26052: Unsupported type 180 for SQL expression on column col3.

I looked into docs for error description:

Cause: The direct path api does not support a SQL expression on a column of that type.

Action: Make sure the types are correct.

Table1 structure is

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 col1                                               Date
 col2                                               Date
 col3                                      NOT NULL TIMESTAMP(6)

Control file is:

unrecoverable
load data
infile '/home/data.dat'
into table table1
fields terminated by '#' optionally enclosed by '"' trailing NULLCOLS (

 col1 "to_date(:col1,'yyyymmdd')"
,col2 "to_date(:col2,'yyyymmdd')"
,col3 "to_timestamp(:col3,'YYYY-MM-DD-HH24.MI.SS.FF6')"

I cant quite understand the message here. Cant I use direct api option while running sql-loader in this case?

TIA Received on Fri Aug 17 2007 - 00:40:00 CDT