Decimal separator changes automtically when storing or reading from XML-DB
KRodlerOct 26 2010 — edited Oct 27 2010I've got the following problem after upgrading from db 10g to 11g.
Both database are configured with NLS_LANG=AUSTRIA_GERMAN.WE8ISO8859P1
An XML ist genereated using PL-SQL and ORACLEs XML-Functions; Result is an XMLType variable in the generating PL-Procedure. In advance I set NLS_TERRITORY to AMERICA to get decimal point as delimiters instead of decimal commas. So far so good, XML validates against its schema. Checking the XML: there are decimal points.
I store the XML to the Repository by CreateResource.
I fetch the file with ftp: and ... there are commas nor points. -- That's new in 11g, and not what I want.
Retrieving the Document with SELECT extract(res, ... brings points or commas depending on the NLS-Setting of the session.
But how can I tell ftp the right NLS-Setting? - Any ideas???
My next idea, Oracle11g is more intelligent the 10g, it recovers the datatype fpr an XMLType-Repository file, probably out of the schema defintion, and converts the number format.
To prevent this, I convert the XMLType variable in the PL/SQL to CLOB (by .getCLobVal); I even assign it to a CLOB varaible. Then I store the CLOB. But same result, again getting the (CLob) file using ftp, all numbers have decimal commas.