-
1. Re: Problems with date formats and decimal delimiters
Another interesting observations we got playing with different locales:
Again the DB's nls_territory is as default (America)
The client's nls_territory is Finland
///////////////////////////////////////////////////////////////////////////////////////////////////////////
INSERT INTO test_kb_table VALUES (1,
sdo_rdf_triple_s (test_kb','http://www.nature.com/nature/Article1',
'http://purl.org/dc/elements/1.1/title','"91,21"^^http://www.w3.org/2001/XMLSchema#float'));
alter session set nls_territory=America;
INSERT INTO test_kb_table VALUES (11,
sdo_rdf_triple_s (test_kb','http://www.nature.com/nature/Article1',
'http://purl.org/dc/elements/1.1/title','"91.45"^^http://www.w3.org/2001/XMLSchema#float'));
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
SELECT x, y
FROM TABLE(SDO_RDF_MATCH(
'(?x :title ?y)',
SDO_RDF_Models('test_kb'),
null,
SDO_RDF_Aliases(SDO_RDF_Alias('','http://purl.org/dc/elements/1.1/')),
null));
gives
X
--------------------------------------------------------------------------------
Y
--------------------------------------------------------------------------------
http://www.nature.com/nature/Article1
91,21
http://www.nature.com/nature/Article1
91.45
Now whatever locale we use, we cannot opperate with values as numbers
SQL> select x, y+3
2 FROM TABLE(SDO_RDF_MATCH(
3 '(?x :title ?y)',
4 SDO_RDF_Models('test_kb'),
5 null,
6 SDO_RDF_Aliases(SDO_RDF_Alias('','http://purl.org/dc/elements/1.1/')),
7 null));
ERROR:
ORA-01722: invalid number
Does anybody know how to ensure consistency of the data?
Anton Naumenko, Researcher, University of Jyvaskyla -
2. Re: Problems with date formats and decimal delimiters
What version of the database are you using?
Melli -
3. Re: Problems with date formats and decimal delimiters
We use Oracle Database 10g Release 2 (10.2.0.1.0) for Microsoft Windows -
4. Re: Problems with date formats and decimal delimiters
This might be a bug. We are investigating and will get back to you as soon as possible.
Melli -
5. Re: Problems with date formats and decimal delimiters
Yes, I have a feeling that this is a bug.
Take a look also on the timestamps.
I can successfully add
INSERT INTO test_kb_table VALUES (1,
sdo_rdf_triple_s ('test_kb','http://www.nature.com/nature/Article1',
'http://purl.org/dc/elements/1.1/title','"2006-05-28T11:41:13"^^http://www.w3.org/2001/XMLSchema#dateTime'))
however the following inserts give errors
INSERT INTO test_kb_table VALUES (3,
sdo_rdf_triple_s ('test_kb','http://www.nature.com/nature/Article1',
'http://purl.org/dc/elements/1.1/title','"2006-05-28T11:41:13Z"^^http://www.w3.org/2001/XMLSchema#dateTime'))
INSERT INTO test_kb_table VALUES (2,
sdo_rdf_triple_s ('test_kb','http://www.nature.com/nature/Article1',
'http://purl.org/dc/elements/1.1/title','"2006-05-28T11:41:13+02:00"^^http://www.w3.org/2001/XMLSchema#dateTime'))
The type xsd:dateTime should support timezones
http://www.w3.org/TR/xmlschema-2/#dateTime
Canonical form should contain "Z" at the end for UTC.
Even if I change format for the session, the errors still popup
alter session set NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM'
We hope you can resolve these issues, as this is critical for a commercial company, that commissioned our research, in order to roll out oracle as a storage for Semantic Web data. -
6. Re: Problems with date formats and decimal delimiters
Hi Anton,
On investigation, rather than a bug it appears to be a case of inadequate documentation. If the way we support data from multiple locales does not address the needs of some applications we would like to get feedback about that.
(1) The timestamp issue: Timestamp with zone information is not supported in 10g release 2 (and has been documented in section 1.2.3.1 of the 10g release 2 documentation). Based on feedback from customers we have included support for timezones in the next database release expected later this year (that release is currently in beta). Could you write to me at melliyal <dot> annamalai <at> oracle <dot> com? We can explore options such as a patch depending on your timeline.
(2) Representation of numeric values in the data should follow the character set specified in the client locale. If the language is 'Finnish', then the data should be represented as "92,00"^^http://www.w3.org/2001/XMLSchema#float . If the language specified by the client locale is American then the data should be represented as "92.00"^^http://www.w3.org/2001/XMLSchema#float .
SQL> select x, y+3
If data is inserted with mixed locales, they are stored internally as equal (because of the support for canonical data values). But when they are retrieved (and they will be in their original form when retrieved) and processed using an arithmetic operator such as '+', the current client locale comes into play - which can be one or the other, but not both. So the operation fails when the data is mixed. Data stored as 92,00 will need the client locale to be Finnish so that arithmetic operator can be applied, and data stored as 92.00 will require the client locale to be American.
2 FROM TABLE(SDO_RDF_MATCH(
3 '(?x :title ?y)',
4 SDO_RDF_Models('test_kb'),
5 null,
6 SDO_RDF_Aliases(SDO_RDF_Alias('','http://purl.org/dc/elements/1.1/')),
7 null));
ERROR:
ORA-01722: invalid number
So the main question is - do you have a need to mix data from different locales?
If you can write to me at melliyal <dot> annamalai <at> oracle <dot> com we can discuss more. We are also very interested in understanding the application more.
Melli -
7. Re: Problems with date formats and decimal delimiters
Hi Melli,
Thank you very much for prompt replies. I am going to continue our discussion about the research we conduct via email after I check our policies for the disclosure of information.
regards, Anton
Register and Participate in Oracle's online communities. Learn from thousand of experts, get answers to your questions and share knowledge with peers.