Home » Developer & Programmer » JDeveloper, Java & XML » Characterset issue in XML (merged) (Oracle 11g Database)
Characterset issue in XML (merged) [message #569804] Thu, 01 November 2012 15:43 Go to next message
wantmannu
Messages: 42
Registered: June 2009
Location: cali
Member

Hi,

Version of DB: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

I have issue while loading xml_data into xmltype field in the table.
Issue is whenever there is a special character like this 'revisions to §6' in xml text then it is 'revisions to §6' when its in the xmltype field of the table.

There is this new character 'Â' appended before every special character.
I have checked the database characterset
Characterset: NLS_CHARACTERSET Value: AL32UTF8

Did anyone have this issue before or do anyone please help me how to get this issue fixed.
Try this simple script

create table xx_testxml(lx xmltype);/

DECLARE
x_item_doc sys.XMLTYPE := NULL;
BEGIN
SELECT XMLELEMENT("SyncItemPrimaryAttribute", 'revisions to §6' )
INTO x_item_doc
FROM dual;
INSERT
INTO xx_testxml VALUES
(
x_item_doc
);
COMMIT;
END;
/
select * from xx_testxml;

Result
<SyncItemPrimaryAttribute>revisions to §6</SyncItemPrimaryAttribute>

Thanks,
Anjali

[Updated on: Thu, 01 November 2012 15:46]

Report message to a moderator

Re: XML DATA to XMLTYPE field issue [message #569811 is a reply to message #569804] Thu, 01 November 2012 21:00 Go to previous messageGo to next message
spacebar
Messages: 26
Registered: February 2007
Junior Member
See if you get the same problem running this:
declare
  x_item_doc sys.xmltype  :=  null;
begin
  select xmlelement("SyncItemPrimaryAttribute", 'revisions to §6' )
   into  x_item_doc
  from dual;
  dbms_output.put_line( x_item_doc.getCLOBVal() );
end;

DBMS_OUTPUT:
------------
 <SyncItemPrimaryAttribute>revisions to §6</SyncItemPrimaryAttribute>

As an additional note, I use charset(WE8ISO8859P1), and I get the same results as you when I use your charset(AL32UTF8):
select dbms_xmlgen.getXMLType(
  q'{select convert('revisions to §6', 'AL32UTF8') myValue from dual}')
from dual
---MYVALUE--
<ROWSET>
 <ROW>
  <MYVALUE>revisions to §6</MYVALUE>
 </ROW>
</ROWSET>


[Updated on: Thu, 01 November 2012 21:15]

Report message to a moderator

Re: XML DATA to XMLTYPE field issue [message #569812 is a reply to message #569811] Thu, 01 November 2012 22:46 Go to previous messageGo to next message
wantmannu
Messages: 42
Registered: June 2009
Location: cali
Member

Hi

I really appreciate the reply you gave. It works fine the way you showed me but I want to insert this data in a XMLTYPE field of a table. Once I do that, I get this additional character 'Â' appended before every special character.

Please let me know if you get the same issue as I mentioned in the sample script.


Thanks,
Anjali
Characterset issue in SQL [message #570010 is a reply to message #569804] Sun, 04 November 2012 12:49 Go to previous messageGo to next message
wantmannu
Messages: 42
Registered: June 2009
Location: cali
Member

Hi,

Version of DB: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

I have issue while loading xml_data into xmltype field in the table.
Issue is whenever there is a special character like this 'revisions to §6' in xml text then it is 'revisions to §6' when its in the xmltype field of the table.

There is this new character 'Â' appended before every special character.
I have checked the database characterset
Characterset: NLS_CHARACTERSET Value: AL32UTF8

Did anyone have this issue before or do anyone pleaseee help me how to get this issue fixed.
Try this simple script

create table xx_testxml(lx xmltype);/

DECLARE
x_item_doc sys.XMLTYPE := NULL;
BEGIN
SELECT XMLELEMENT("SyncItemPrimaryAttribute", 'revisions to §6' )
INTO x_item_doc
FROM dual;
INSERT
INTO xx_testxml VALUES
(
x_item_doc
);
COMMIT;
END;
/
select * from xx_testxml;

Result
<SyncItemPrimaryAttribute>revisions to §6</SyncItemPrimaryAttribute>

Thanks,
Anjali
Re: Characterset issue in SQL [message #570011 is a reply to message #570010] Sun, 04 November 2012 12:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is useless to repeat the same question, topics will be merged.

Regards
Michel
Re: Characterset issue in SQL [message #570084 is a reply to message #570011] Mon, 05 November 2012 08:49 Go to previous messageGo to next message
wantmannu
Messages: 42
Registered: June 2009
Location: cali
Member

Hi Michael,

Sorry to post this issue again. Just want to make sure everyone looks into it and suggest me something.

But as you said, it would not work.

Thanks,
Anjali
Re: Characterset issue in SQL [message #570090 is a reply to message #570084] Mon, 05 November 2012 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Trust me, every Senior members have seen your topic but maybe no one knows the answer or is able to reproduce your case.
I have few time for the moment to investigate on it but I think others already did (but failed to get a solution).

One information: what is the NLS_LANG value of the process that started the instance?

Regards
Michel
Re: Characterset issue in SQL [message #570092 is a reply to message #570090] Mon, 05 November 2012 10:11 Go to previous messageGo to next message
wantmannu
Messages: 42
Registered: June 2009
Location: cali
Member

NLS_LANGUAGE value is AMERICAN.
Re: Characterset issue in SQL [message #570095 is a reply to message #570092] Mon, 05 November 2012 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not NLS_LANGUAGE, NLS_LANG.

Regards
Michel
Re: Characterset issue in SQL [message #570110 is a reply to message #570095] Mon, 05 November 2012 13:01 Go to previous messageGo to next message
wantmannu
Messages: 42
Registered: June 2009
Location: cali
Member

Hi Michael,

Tried searching for NLS_LANG in NLS_SESSION_PARAMETERS and NLS_DATABASE_PARAMETERS.
Did not find any NLS_LANG field. But found these below data.

NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
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 TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_RDBMS_VERSION 11.2.0.2.0

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
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_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE



Re: Characterset issue in SQL [message #570114 is a reply to message #570110] Mon, 05 November 2012 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot see it at SQL level, you have to havc a look at OS level, in your OS process environment.

Regards
Michel
Re: Characterset issue in SQL [message #570123 is a reply to message #570114] Mon, 05 November 2012 14:55 Go to previous messageGo to next message
wantmannu
Messages: 42
Registered: June 2009
Location: cali
Member

At OS Level I found LANG - en_US.UTF-8
I found LANG from unix box searching for env. Tried echo $NLS_LANG ..NLS_LANG: Undefined variable.


Re: Characterset issue in SQL [message #570157 is a reply to message #570123] Tue, 06 November 2012 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Tried echo $NLS_LANG ..NLS_LANG: Undefined variable.


So it is the default value: AMERICAN_AMERICA.US7ASCII, and so your error.
Set it to AMERICAN_AMERICA.AL32UTF8, restart the instance and I think your problem will be solved.

Regards
Michel
Re: Characterset issue in SQL [message #570216 is a reply to message #570157] Tue, 06 November 2012 16:07 Go to previous messageGo to next message
wantmannu
Messages: 42
Registered: June 2009
Location: cali
Member

The issue still exist after setting the NLS_LANG to AMERICAN_AMERICA.AL32UTF8 and restaring the instance.



[Edit MC: change typo NFL to NLS for readability]

[Updated on: Thu, 08 November 2012 01:34] by Moderator

Report message to a moderator

Re: Characterset issue in SQL [message #570218 is a reply to message #570216] Tue, 06 November 2012 17:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
wantmannu wrote on Tue, 06 November 2012 14:07
The issue still exist after setting the NFL_LANG to AMERICAN_AMERICA.AL32UTF8 and restaring the instance.


That should be NLS, not NFL.
Re: Characterset issue in SQL [message #570224 is a reply to message #570218] Tue, 06 November 2012 23:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sure... Feel free to modify my post when you see such typo.

Regards
Michel
Re: Characterset issue in SQL [message #570299 is a reply to message #570224] Wed, 07 November 2012 13:45 Go to previous messageGo to next message
wantmannu
Messages: 42
Registered: June 2009
Location: cali
Member

Sorry for late response. Yes its a typo..supposed to be NLS_LANG and not NFL_LANG.
I was dragged too much into NFL these days by my colleagues Razz
Re: Characterset issue in SQL [message #570341 is a reply to message #570299] Thu, 08 November 2012 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Were you locally connect to the database when you did your latest test or remotely?

Regards
Michel
Re: Characterset issue in SQL [message #570378 is a reply to message #570341] Thu, 08 November 2012 08:20 Go to previous messageGo to next message
wantmannu
Messages: 42
Registered: June 2009
Location: cali
Member

I have been locally connecting to the database all the way.
Re: Characterset issue in SQL [message #570379 is a reply to message #570378] Thu, 08 November 2012 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
That is "connect usr/pass" without "@", we are clear?
With which client tool did you make your tests?
I couldn't reproduce your problem till now.

Regards
Michel
Re: Characterset issue in SQL [message #570380 is a reply to message #570379] Thu, 08 November 2012 08:30 Go to previous messageGo to next message
wantmannu
Messages: 42
Registered: June 2009
Location: cali
Member

I am using sql devloper. You can directly run my full script in Toad or Sql devloper or Sql*plus to test my case.
Re: Characterset issue in SQL [message #570382 is a reply to message #570380] Thu, 08 November 2012 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I ran it in SQL*Plus/Windows but have not your issue.
I will test it on Unix/Linux db to see.

Regards
Michel
Re: Characterset issue in XML (merged) [message #570532 is a reply to message #569804] Sun, 11 November 2012 21:27 Go to previous messageGo to next message
wantmannu
Messages: 42
Registered: June 2009
Location: cali
Member

Hi Michael,

How did it go in Unix/Linus..
Re: Characterset issue in XML (merged) [message #570539 is a reply to message #570532] Sun, 11 November 2012 23:54 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry I was very busy and forgot to do it.
I will try as soon as possible.

Regards
Michel
Previous Topic: Create a Databound Hierarchy Viewer
Next Topic: updating an element text in XML document
Goto Forum:
  


Current Time: Thu Mar 28 03:14:17 CDT 2024