| Unable to see/insert Chinees characters in Oracle db [message #587292] |
Fri, 14 June 2013 00:04  |
pradies
Messages: 250
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 #587296 is a reply to message #587294] |
Fri, 14 June 2013 00:44  |
pradies
Messages: 250
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 #588622 is a reply to message #587319] |
Thu, 27 June 2013 02:59 |
pradies
Messages: 250
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 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
