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

    Problems with date formats and decimal delimiters

    563141
      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
          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
            Mannamal-Oracle
            What version of the database are you using?

            Melli
            • 3. Re: Problems with date formats and decimal delimiters
              563141
              We use Oracle Database 10g Release 2 (10.2.0.1.0) for Microsoft Windows
              • 4. Re: Problems with date formats and decimal delimiters
                Mannamal-Oracle
                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
                  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.
                  • 6. Re: Problems with date formats and decimal delimiters
                    Mannamal-Oracle
                    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
                    • 7. Re: Problems with date formats and decimal delimiters
                      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