Using SQL DEveloper v.3.2.20.09 on Windows XP and connecting to Oracle Database 11g Enterprise Edition Release 126.96.36.199.0 - 64bit Production.
SQL Developer encoding is set to Cp1252 (my understanding is this has nothing to do with nls_lang and used for encoding of files in sqldeveloper)
SQL Developer NLS Language params set to Canada English.
Database NLS parameters are:
"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"
I didn't have an NLS_LANG user env variable set, so i set it to "AMERICAN_AMERICA.WE8MSWIN1252"
Problem i'm having is editing my xmltype data... i have some french accented characters in there, but when saved through SQLDeveloper, they come out as:
Étape par étape -> Ã‰tape par Ã©tape
I do have an encoding on the xml file in the xmltype column set to UTF-8... but from what i read, this has no effect when reading or writing in 11g...
I ran a dump(col, 1016) on my table and got the following:
I find it odd it doesnt tell me what character set is used. If i run a dump 1016 on a varchar2 on the same table, i get:
Typ=1 Len=14 CharacterSet=AL32UTF8: 53,74,65,c3,a9,70,68,61,6e,65,c3,a9,c3,a0
Any ideas? This is frustrating.... xml data gets corrupted everytime it's edited in SQLDeveloper.
Thanks for the help
Just to add to this... noticed I can save and display accented characters just fine in a varchar2 field in sqldeveloper... only when i edit an xmltype do i have this problem of these characters not properly converting. Any ideas? Bug in SQLDeveloper perhaps? Or 11gr2? We are still running on 188.8.131.52, but will be upgrading to 184.108.40.206 next week.
xml data gets corrupted everytime it's edited in SQLDeveloper.
. . .
I ran a dump(col, 1016) on my table and got the following:.
. .0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 . . .
That looks like binary (raw) data; not text data in any character set.
Actual xmltype or text data in character sets I am familiar with doesn't allow or include binary 0 since that would be NULL.
I suggest you create a SIMPLE example (e.g. just a few characters) that demonstrates your problem. Use a table with both a VARCHAR2 and an XMLTYPE column, put the same data in both and show a dump of the two columns as well as what you get in sql developer.
Thanks for the reply...
create table sample_charset(theString varchar2(2 char), theXml xmltype); insert into sample_charset values('éà',xmltype('<xml>éà</xml>')); commit;
select * from sample_charset;
"éà" <xml>Ã©Ã </xml>
select dump(theString, 1016) as theStringDump, dump(theXml,1016) as theXmlDump from sample_charset;
"Typ=1 Len=4 CharacterSet=AL32UTF8: c3,a9,c3,a0"
"Typ=58 Len=120: 0,0,0,1,10,4,8c,a8,0,0,0,1,10,e1,a8,60,0,0,0,1,10,dc,36,f8,0,0,0,1,10,95,bf,28,3b,9a,ca,0,0,0,f,a0,0,0,f,a0,0,1,0,4,0,0,0,1,9,84,d5,24,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,1,10,f,91,28,0,0,0,1,10,db,a8,b8"