Home » RDBMS Server » Server Administration » Unable to see/insert Chinees characters in Oracle db (oracle 10.2.0.4, AIX)
Unable to see/insert Chinees characters in Oracle db [message #587292] Fri, 14 June 2013 00:04 Go to next message
pradies
Messages: 243
Registered: May 2008
Senior Member
Hi,

We are getting problem with the Chinese character set. My current character set is as follows.

PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE                                                     AMERICAN
NLS_TERRITORY                                                    AMERICA
NLS_CURRENCY                                                     $
NLS_ISO_CURRENCY                                                 AMERICA
NLS_NUMERIC_CHARACTERS                                           .,
NLS_CALENDAR                                                     GREGORIAN
NLS_DATE_FORMAT                                                  YYYY/MM/DD HH24:MI:SS
NLS_DATE_LANGUAGE                                                AMERICAN
NLS_CHARACTERSET                                                 WE8ISO8859P15
NLS_SORT                                                         BINARY
NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                                                $
NLS_NCHAR_CHARACTERSET                                           AL16UTF16
NLS_COMP                                                         BINARY
NLS_LENGTH_SEMANTICS                                             BYTE
NLS_NCHAR_CONV_EXCP                                              FALSE



My column description for the table product is as follows.

PART_NBR	VARCHAR2 (30 Byte)		
PART_DESC	NVARCHAR2 (2000)	


Problem is : when trying to insert Chinese character using the insert command below

 insert into product(part_nbr,part_desc,cust_name) values('322341',unistr('功'),'test'); 


I am getting the value when selecting the same record using the select command

select a.part_nbr,a.part_desc,a.cust_name from  product a where a.part_nbr='322341'
322341	¿	test


[Confusion ]
When I running this command on TOAD

select a.rowid,a.part_nbr,a.part_desc,a.cust_name from  product a where a.part_nbr='322341'

and manually editing/inserting '功' character in output from select command above. After that I am able to get the same Chinese character when I am running select next time.

322341	功	test



I Google for this error/problem but didn't get any specific error resolution.

kindly tell me what we can do for this error.


Thanks in Advance
Pradeep
Re: Unable to see/insert Chinees characters in Oracle db [message #587294 is a reply to message #587292] Fri, 14 June 2013 00:18 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I Google for this error/problem but didn't get any specific error resolution.


I'm very surprised you didn't find anything as this is a FAQ (for Chinese or Indian or Arabic characters).

Anyway, with NLS_CHARACTERSET=WE8ISO8859P15 you cannot use Chinese characters as this character set does not contain them.

Regards
Michel
Re: Unable to see/insert Chinees characters in Oracle db [message #587296 is a reply to message #587294] Fri, 14 June 2013 00:44 Go to previous messageGo to next message
pradies
Messages: 243
Registered: May 2008
Senior Member
Hi Michel,

thanks for your reply. Please let me know which NLS_CHARACTERSET for Chinese, with new character set, will I be able to use other older characters or I need to revert back to older character set.

Also please tell me why I am able see the exact value when I am inserting manually check tag CONFUSION.

thanks again.

Pradeep
Re: Unable to see/insert Chinees characters in Oracle db [message #587297 is a reply to message #587296] Fri, 14 June 2013 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please let me know which NLS_CHARACTERSET for Chinese, with new character set


AL32UTF8 is definitively one, others work too, refer to the documentation.

Quote:
will I be able to use other older characters or I need to revert back to older character set.


I don't know what you mean by this.
What I can is that if you enter Chinese characters in a database with this current character set then the data are screwed.

Quote:
Also please tell me why I am able see the exact value when I am inserting manually check tag CONFUSION.


What is "check tag CONFUSION"?
Anyway, say you are lucky.

Regards
Michel
Re: Unable to see/insert Chinees characters in Oracle db [message #587315 is a reply to message #587297] Fri, 14 June 2013 01:56 Go to previous messageGo to next message
pradies
Messages: 243
Registered: May 2008
Senior Member
Hi Michel,

thanks for your response.

Character set conversion works for me. I converted to AL32UTF8 charcter set and now I am able to see the same record as
I inserted..

thanks Alot.

Pradeep
Re: Unable to see/insert Chinees characters in Oracle db [message #587319 is a reply to message #587315] Fri, 14 June 2013 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I converted to AL32UTF8 charcter set


What do you mean "converted"? What did you do exactly?

Regards
Michel

[Updated on: Fri, 14 June 2013 02:25]

Report message to a moderator

Re: Unable to see/insert Chinees characters in Oracle db [message #588622 is a reply to message #587319] Thu, 27 June 2013 02:59 Go to previous messageGo to next message
pradies
Messages: 243
Registered: May 2008
Senior Member
Hi Michel,

I mean with "converted" is I changed the character set of database.

As this testing was done testing environment, I am stuck on prod becuase I can't change character set on prod.

As I am checking the same on google, I found that we can upload the same characters using sqlldr with some characterset parameter , in sqlldr control file we can use some characterset conversion parameter.

Like I am trying this .

LOAD DATA
CHARACTERSET AL32UTF8
INFILE 'C:\Users\Desktop\PRODUCT.xlsx' 
BADFILE 'C:\Users\Desktop\PRODUCT.bad'
DISCARDFILE 'C:\Users\Desktop\PRODUCT.dsc'
INTO TABLE "scott"."TEST"
INSERT
FIELDS TERMINATED BY ','
(PART_NBR,
PART_DESC,
CUST_NAME)


data is uploading using the same ctl file but not correctly. '?' is showing in the table during selection.


Kindly tell me if I am wrong in the above control file.


Regards

Pradeep
Re: Unable to see/insert Chinees characters in Oracle db [message #588634 is a reply to message #588622] Thu, 27 June 2013 04:12 Go to previous message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If the file is coded in AL32UTF8 then the control file is correct.
Now maybe your database character set does not support some if the characters in the file and so they are replace by a "replacement character" which seems to be "?" for you (it depends on the target character set).

Regards
Michel
Previous Topic: how to create oracle 8.1.7 database if dbca dbasssist.cl is not working
Next Topic: Segment shrink and FBIs
Goto Forum:
  


Current Time: Sat Dec 20 20:38:10 CST 2014

Total time taken to generate the page: 0.07731 seconds