7 Replies Latest reply on Feb 27, 2007 10:50 AM by 563141

Problems with date formats and decimal delimiters

563141 Feb 21, 2007 1:53 PM

Hi

We are trying the Oracle database to store RDF data for maintenance of industrial resources. We have problems to initially load triples into the database.

NLS database settings are default:
nls_language=American
nls_territory=America

The client locale is Finnish

We got errors for values of typed literals when loading raw RDF data, e.g.
"2006-05-28T11:41:13+03:00"^^http://www.w3.org/2001/XMLSchema#dateTime
"92.00"^^http://www.w3.org/2001/XMLSchema#float

After altering the session on the client side to nls_territory=America, values with "." as decimal delimiter were loaded successfully, e.g.
"92.00"^^http://www.w3.org/2001/XMLSchema#float

However, we still struggle to load triples with timezone indicator. Could anybody suggest proper way of tackling this problem. Take into account also that later we might have to incrementally add timevalues formatted according to different locales.

Looking forward, Anton Naumenko, Researcher, University of Jyvaskyla, Finland

1. Re: Problems with date formats and decimal delimiters

563141 Feb 27, 2007 10:41 AM (in response to 563141)

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
Like Show 0 Likes(0)

Actions
2. Re: Problems with date formats and decimal delimiters

Mannamal-Oracle Feb 22, 2007 6:27 AM (in response to 563141)

What version of the database are you using?

Melli
Like Show 0 Likes(0)

Actions
3. Re: Problems with date formats and decimal delimiters

563141 Feb 22, 2007 8:46 AM (in response to Mannamal-Oracle)

We use Oracle Database 10g Release 2 (10.2.0.1.0) for Microsoft Windows
Like Show 0 Likes(0)

Actions
4. Re: Problems with date formats and decimal delimiters

Mannamal-Oracle Feb 23, 2007 2:51 AM (in response to 563141)

This might be a bug. We are investigating and will get back to you as soon as possible.

Melli
Like Show 0 Likes(0)

Actions
5. Re: Problems with date formats and decimal delimiters

563141 Feb 26, 2007 12:32 PM (in response to 563141)

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.
Like Show 0 Likes(0)

Actions
6. Re: Problems with date formats and decimal delimiters

Mannamal-Oracle Feb 27, 2007 5:25 AM (in response to 563141)

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
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
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.

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
Like Show 0 Likes(0)

Actions
7. Re: Problems with date formats and decimal delimiters

563141 Feb 27, 2007 10:50 AM (in response to 563141)

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
Like Show 0 Likes(0)

Actions

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.