Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

XMLELEMENT and decimal numbers

Peter GjelstrupNov 20 2012 — edited Nov 21 2012
Hello members,

I just encountered a problem, which indicate that XMLELEMENT relies on NLS settings.
SQL> alter session set nls_numeric_characters = ',.'
  2  /

Session altered.

SQL> select xmlelement("x",1.23) from dual
  2  /

XMLELEMENT("X",1.23)
-----------------------------------------------------------------
<x>1,23</x>

SQL> alter session set nls_numeric_characters= '.,'
  2  /

Session altered.

SQL> select xmlelement("x",1.23) from dual
  2  /

XMLELEMENT("X",1.23)
-----------------------------------------------------------------
<x>1.23</x>

SQL> select *from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL>
This kind of surprises me, especially since according to XML DB Developer's Guide 10g Release 2 (10.2), W3C standards are honored since 10gR2:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#i1028612
The XML Schema standard specifies that dates and timestamps in XML data be in standard formats.
XML generation functions in Oracle XML DB produce XML dates and timestamps according to this standard.

In releases prior to Oracle Database 10g Release 2, the database settings for date and timestamp formats were used for XML, instead of the XML Schema standard formats.
You can reproduce this previous behavior by setting the database event 19119, level 0x8, as follows:
Can anybody confirm, maybe even explain above behaviour?
- Maybe I am just fooling myself, somehow?


Sorry for asking, but I did try to search (a little) for an answer ;)

Best regards
Peter
This post has been answered by Solomon Yakobson on Nov 20 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 19 2012
Added on Nov 20 2012
7 comments
1,126 views