Hello,
I've got xml data stored in CLOB field. Database decimal character is dot. The numeric data stored in xml field is using dot as decimal separator. I prepared query that changes xml data into table like record set:
create table XML_REPORT_DATA
(
SEQ_NO NUMBER not null,
data CLOB
)
simplification of query:
SELECT xt.*,x.data
FROM XML_REPORT_DATA x,
XMLTABLE(
'/TOP/PARTS/PART'
PASSING xmltype(x.data)
COLUMNS
"DECIMAL_DATA" NUMBER path 'DECIMAL_DATA'
) xt
inside field named data of type CLOB there is xml data
<TOP><PARTS><PART><DECIMAL_DATA>194.4</DECIMAL_DATA></PART></PARTS></TOP>
When I run query inPL/SQL developper withalter session set NLS_NUMERIC_CHARACTERS = ". "; everything works fine. When I use default settings for my regional settings alter session set NLS_NUMERIC_CHARACTERS = ", "; then i get error
ora-01722: invalid number
It is not a problem, when I use this query with pl/sql developer but when I want get the data from oracle into excel using microsoft query i always get error ora-01722: invalid number. I tried using oracle's and microsoft's odbc. In oracle's odbc definition I tried to set nsl setting to US but still nothing.
My regional windows settings are set to Poland - comma as decimal separator.
My oracle nls setting are set to Poland/ Polish.
Artur
You could try poking around with your NLS_LANG setting in the registry. Or creating a logon trigger that changes NLS_NUMERIC_CHARACTERS to ".,".
Or perhaps easier and more robust:
- Pass the value as varchar2 in your XMLTable operator
- Convert it to a number with to_number, passing in the appropriate NLS settings:
create table XML_REPORT_DATA
(
SEQ_NO NUMBER not null,
data CLOB
);
SELECT xt.*,x.data
FROM XML_REPORT_DATA x,
XMLTABLE(
'/TOP/PARTS/PART'
PASSING xmltype(x.data)
COLUMNS
"DECIMAL_DATA" NUMBER path 'DECIMAL_DATA'
) xt;
insert into XML_REPORT_DATA values (1,
'<TOP><PARTS><PART><DECIMAL_DATA>194.4</DECIMAL_DATA></PART></PARTS></TOP>');
alter session set nls_numeric_characters = ',.';
SELECT xt.decimal_data
FROM XML_REPORT_DATA x,
XMLTABLE(
'/TOP/PARTS/PART'
PASSING xmltype(x.data)
COLUMNS
"DECIMAL_DATA" number path 'DECIMAL_DATA'
) xt;
SQL Error: ORA-01722: invalid number
SELECT to_number(xt.decimal_data, 'FM999D9', 'nls_numeric_characters = ''.,''') num
FROM XML_REPORT_DATA x,
XMLTABLE(
'/TOP/PARTS/PART'
PASSING xmltype(x.data)
COLUMNS
"DECIMAL_DATA" varchar2(100) path 'DECIMAL_DATA'
) xt;
NUM
194.4