Home » RDBMS Server » Server Utilities » Error in loading chinese characters (Oracle 10g RHEL 5)
Error in loading chinese characters [message #416192] Fri, 31 July 2009 01:51 Go to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
Hi All,

I try to import a sample chinese characters into my Oracle 10g Database through sql loader but throws an error saying "Field in data file exceeds maximum length".

My database char set is AL32UTF8 and i had set the
NLS_LANG=American_America.AL32UTF8.
I dont see any pit falls in my config.
Below is my table structure.

SQL> desc china2
Name Type

NUM NVARCHAR2(100)
NAME NVARCHAR2(1200)

Attached is the control file i used.

Thanks in Adv.
Sanjay
  • Attachment: control.ctl
    (Size: 0.15KB, Downloaded 295 times)

[Updated on: Fri, 31 July 2009 01:54]

Report message to a moderator

Re: Error in loading chinese characters [message #416196 is a reply to message #416192] Fri, 31 July 2009 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NxCHAR does not use database character set but national database character set.
Each character is (most likely) on 2 bytes.
Count the number of characters in your data, multiply by 2 and check if they fit in the fields.

Regards
Michel
Re: Error in loading chinese characters [message #417330 is a reply to message #416196] Fri, 07 August 2009 05:23 Go to previous messageGo to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
Hello Michael,

thank you for immediate reply, sorry i waz not feeling gud these days and could not reply back immediately.

Wel, i try to insert a single chinese character but was unable to do as it was not loading into the database through SQLLDR.


How can we further go ahead.plz assist,,,

thank you in adv.
Sanjay
Re: Error in loading chinese characters [message #417332 is a reply to message #417330] Fri, 07 August 2009 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Count the number of characters in your data, multiply by 2 and check if they fit in the fields.

Regards
Michel
Re: Error in loading chinese characters [message #417334 is a reply to message #417332] Fri, 07 August 2009 05:45 Go to previous messageGo to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
Hi ,
please Brief the above quote, and as i m using a single character and is placing into large field, how can that create the issue..!!

thank you for reply..
Re: Error in loading chinese characters [message #417336 is a reply to message #417334] Fri, 07 August 2009 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste the log file.

Regards
Michel
Re: Error in loading chinese characters [message #417338 is a reply to message #417334] Fri, 07 August 2009 06:42 Go to previous messageGo to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
Attached is the log file which tells the status of my issue.

And the table structure is below.
SQL> desc china2
Name Type

NUM NVARCHAR2(1000)
NAME NVARCHAR2(1200)

  • Attachment: control.log
    (Size: 2.51KB, Downloaded 247 times)

[Updated on: Fri, 07 August 2009 06:47]

Report message to a moderator

Re: Error in loading chinese characters [message #417339 is a reply to message #417338] Fri, 07 August 2009 06:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your OS character set?
What is the data file character set?

Regards
Michel
Re: Error in loading chinese characters [message #417340 is a reply to message #417338] Fri, 07 August 2009 07:12 Go to previous messageGo to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
My OS char set is English
and i used excel file to import the data into the database, so not sure what the char set of excel datafile, but yes excel file was supporting chinese datas.
I m using RHEL-5 64 BIT OS

Re: Error in loading chinese characters [message #417341 is a reply to message #417340] Fri, 07 August 2009 07:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
My OS char set is English

Ah ah, that's the problem.
As doc says (http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_control_file.htm#sthref648):
Quote:
The SQL*Loader control file itself is assumed to be in the character set specified for your session by the NLS_LANG parameter.

So you have to change your NLS_LANG parameter.
In addition:
Quote:
By default, the datafile is in the character set defined by the NLS_LANG parameter.

So if it is in UTF8 try to add CHARACTERSET UTF8 into your control file.


Regards
Michel
Re: Error in loading chinese characters [message #417889 is a reply to message #416192] Wed, 12 August 2009 00:44 Go to previous messageGo to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
I tried adding "CHARACTERSET UTF8" to my control file and execute but was throwing the same error "Field in data file exceeds maximum length".

Attached control file contains all the info i m including to execute through 'SQLLDR' utility. please assist where may be the root cause.!!!

Thanks
Sanjay
  • Attachment: control.ctl
    (Size: 0.17KB, Downloaded 262 times)
Re: Error in loading chinese characters [message #417900 is a reply to message #417889] Wed, 12 August 2009 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you change your NLS_LANG parameter?
Is your data file really in UTF8?

Regards
Michel
Re: Error in loading chinese characters [message #417926 is a reply to message #417889] Wed, 12 August 2009 02:25 Go to previous messageGo to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
I tried executing a unicode text file in which chinese characters where present.

i got the below status from the log file.


SQL*Loader: Release 10.2.0.1.0 - Production on Wed Aug 12 12:46:37 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Control File: control.ctl
Character Set UTF8 specified for all input.

Data File: unicode.txt
Bad File: unicode.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table CHINA2, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
NUM FIRST * , O(") CHARACTER
NAME NEXT * , O(") CHARACTER


Table CHINA2:
4 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 33024 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 4
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Wed Aug 12 12:46:37 2009
Run ended on Wed Aug 12 12:46:40 2009

Elapsed time was: 00:00:03.24
CPU time was: 00:00:00.01



But i dint see the correct chinese letters in the database which is displaying something like these below.

��" 1 0 0
" " 2�" " "

" 2 0 0
" " �" " "

" 3 0 0
" " o`" " "


Wel I understand unicode text file as the UTF datafile.
Please correct me on this.

Thanks,
Sanjay
Re: Error in loading chinese characters [message #418000 is a reply to message #417926] Wed, 12 August 2009 08:06 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


How your verfiying database data??
Re: Error in loading chinese characters [message #418007 is a reply to message #418000] Wed, 12 August 2009 08:16 Go to previous message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
Through SQLPLUS i m verifying the data's stored in the database.
Previous Topic: how to import
Next Topic: Collective Export and Import
Goto Forum:
  


Current Time: Sat Dec 03 20:24:09 CST 2016

Total time taken to generate the page: 0.09839 seconds