4 Replies Latest reply on Jun 19, 2012 11:34 AM by 931085

    How to save XML timeformat to oracle without loosing "timezone-compensati"

    931085
      I hope i post this in the correct forum and I provide enough info for you to understand what I'm looking for...

      Some time ago I got some help here about how to read a incoming XML-file and insert data into a table, where the Element-tag describes the tablename and the Attributes describes the column-names. See post : [ https://forums.oracle.com/forums/message.jspa?messageID=10284116#10284116]

      During testing we discovered that on some incoming messages there where Date-fields, the date/time-values has the format '2012-04-19T14:00:05+02:00' which causing us problems when saving the value to our oracle table.

      This is (simplifyed) how the message look like:
      <?xml version="1.0" encoding="UTF-8"?>
      <PickResult>
      <Metadata>
      <TransactionIdentity>"00000011"</TransactionIdentity>
      <MessageTypeIdentity>"MyMessageType"</MessageTypeIdentity>
      <MessagePartyIdentity>"02"</MessagePartyIdentity>
      </Metadata>
      <Data>
      <PickResult PickingTime="2012-04-19T14:00:05+02:00" ProductDate="2012-04-20" Picker="MyEmployee" />
      </Data>
      </PickResult

      I've tried different types on the PICKINGTIME-column like "DATE", "TIMESTAMP" and "TIMESTAMP(6) WITH LOCAL TIME ZONE" but I still get oracle errors like
      "ORA-01858: a non-numeric character was found where a numeric was expected"
      "ORA-01861: literal does not match format string"
      when I run the statement.

      If I declare the PICKINGTIME-column as "DATE" it works without errors but it doesn't add the two extra hours(+02:00) to the time which is very importent since the Picking time must be accurate.


      This is the how the "final" Insert-statement look like which I need to modify somehow to make it accept the timeformat from the message:

      INSERT INTO PickResult( PICKER, PICKINGTIME, PRODUCTDATE)
      SELECT PICKER, PICKINGTIME, PRODUCTDATE
      FROM XMLTable('/PickResult/Data/descendant::PickResult'
      PASSING :1 COLUMNS
      PICKER varchar2(35) path '@PICKER'
      , PICKINGTIME date path '@PICKINGTIME'
      , PRODUCTDATE date path '@PRODUCTDATE')

      Can anyone provide me with some input how to solve this problem because I'm totally stucked...

      ps. please forgive me for my poor "technical" english.
      //Peter
        • 1. Re: How to save XML timeformat to oracle without loosing "timezone-compensati"
          odie_63
          Hi,
          PickingTime="2012-04-19T14:00:05+02:00"
          xs:dateTime format is mapped to TIMESTAMP (WITH TIME ZONE) in SQL.

          This works for me :
          SQL> select * from v$version;
           
          BANNER
          ----------------------------------------------------------------
          Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
          PL/SQL Release 10.2.0.5.0 - Production
          CORE     10.2.0.5.0     Production
          TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
          NLSRTL Version 10.2.0.5.0 - Production
           
          SQL> 
          SQL> SELECT PICKER, PICKINGTIME, PRODUCTDATE
            2  FROM XMLTable('/PickResult/Data/descendant::PickResult'
            3  PASSING xmltype('<?xml version="1.0" encoding="UTF-8"?>
            4  <PickResult>
            5  <Metadata>
            6  <TransactionIdentity>"00000011"</TransactionIdentity>
            7  <MessageTypeIdentity>"MyMessageType"</MessageTypeIdentity>
            8  <MessagePartyIdentity>"02"</MessagePartyIdentity>
            9  </Metadata>
           10  <Data>
           11  <PickResult PickingTime="2012-04-19T14:00:05+02:00" ProductDate="2012-04-20" Picker="MyEmployee" />
           12  </Data>
           13  </PickResult>')
           14  COLUMNS
           15    PICKER      varchar2(35)              path '@Picker'
           16  , PICKINGTIME timestamp with time zone  path '@PickingTime'
           17  , PRODUCTDATE date                      path '@ProductDate'
           18  )
           19  ;
           
          PICKER                              PICKINGTIME                                       PRODUCTDATE
          ----------------------------------- ------------------------------------------------- -----------
          MyEmployee                          19/04/12 14:00:05,000000 +02:00                   20/04/2012
           
          • 2. Re: How to save XML timeformat to oracle without loosing "timezone-compensati"
            931085
            Hmm... am I missing something here...

            If I only do the select I also get the same result as you but if I add the INSERT statement first, I loose those 2 hours.
            The PICKINGTIME column is of type "TIMESTAMP(6) WITH LOCAL TIME ZONE"

            Only select : [19.04.2012 14:00:05,000000 +02:00]
            Using Insert : [19.04.2012 14:00:05,000000]

            INSERT INTO MyTable( EMPLOYEEIDENTITY, PICKINGTIME, PRODUCTDATE)
            SELECT PICKER, PICKINGTIME, PRODUCTDATE
            FROM XMLTable('/PickResult/Data/descendant::PickResult'
            PASSING xmltype('<?xml version="1.0" encoding="UTF-8"?>
            <PickResult>
            <Metadata>
            <TransactionIdentity>"00000011"</TransactionIdentity>
            <MessageTypeIdentity>"MyMessageType"</MessageTypeIdentity>
            <MessagePartyIdentity>"02"</MessagePartyIdentity>
            </Metadata>
            <Data>
            <PickResult PickingTime="2012-04-19T14:00:05+02:00" ProductDate="2012-04-20" Picker="Peter" />
            </Data>
            </PickResult>')
            COLUMNS
            PICKER varchar2(35) path '@Picker'
            , PICKINGTIME timestamp with time zone path '@PickingTime'
            , PRODUCTDATE date path '@ProductDate');
            • 3. Re: How to save XML timeformat to oracle without loosing "timezone-compensati"
              odie_63
              The PICKINGTIME column is of type "TIMESTAMP(6) WITH LOCAL TIME ZONE"
              Then make it "TIMESTAMP WITH TIME ZONE" instead.

              See the difference here : http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_sqltypes.htm#sthref439
              • 4. Re: How to save XML timeformat to oracle without loosing "timezone-compensati"
                931085
                I thougth I've tried all combinations but obviously not...
                Thanks for pointing me n the right direction...