2 Replies Latest reply: Jan 8, 2013 6:10 PM by 341754 RSS

    Timestamp with time zone format xmlforest with object type

    341754
      Hi,

      We are using oracle 11.2.01 XDB. I am working on to generate XMLs with date node having time zone information. The output date format is not what I want.

      Here is the object type:

      CREATE OR REPLACE TYPE TYP_01 AS OBJECT (
      "RecType" VARCHAR2(2),
      "Cycle" VARCHAR2(6),
      "FileDate" TIMESTAMP WITH TIME ZONE,
      "TermsAccepted" VARCHAR2(3))FINAL INSTANTIABLE

      then I use this object type to create XML instance, please note the time format in the output: what I need is *2013-01-08T02:25:25-08:00*
      select xmlforest(
      TYP_01(
      '01',
      '006390',
      sysdate,
      'ACC') AS APPL01)
      from dual


      <APPL01>
      <RecType>01</RecType>
      <Cycle>006390</Cycle>
      <FileDate> *08-JAN-13 02.25.25.000000 PM -08:00* </FileDate>
      <TermsAccepted>ACC</TermsAccepted>
      </APPL01>

      I tried the alter session set nls_date_format and nls_timestamp_format options.

      Please help!

      Edited by: Hedy on Jan 8, 2013 3:34 PM
        • 1. Re: Timestamp with time zone format xmlforest with object type
          odie_63
          NLS_TIMESTAMP_TZ_FORMAT :
          SQL> alter session set nls_timestamp_tz_format = 'YYYY-MM-DD"T"HH24:MI:SSTZR';
           
          Session altered
           
          SQL> select xmlserialize(content
            2           xmlforest(
            3            TYP_01('01', '006390', sysdate, 'ACC') AS APPL01
            4           )
            5           indent
            6         )
            7  from dual;
           
          XMLSERIALIZE(CONTENTXMLFOREST(
          --------------------------------------------------------------------------------
          <APPL01>
            <RecType>01</RecType>
            <Cycle>006390</Cycle>
            <FileDate>2013-01-09T00:48:42+01:00</FileDate>
            <TermsAccepted>ACC</TermsAccepted>
          </APPL01>
           
          I guess the object type is part of a bigger design, but do you need to use objects at all?

          For example, you could simply use TO_CHAR with the same format mask to achieve the same :
          select xmlforest(
                   to_char(systimestamp, 'YYYY-MM-DD"T"HH24:MI:SSTZR') as "FileDate"
                 )
          from dual;
          or let the default behaviour applies, i.e. Timestamps are converted to the ISO dateTime format (including the fractional part) :
          select xmlforest(
                   systimestamp as "FileDate"
                 )
          from dual;
          • 2. Re: Timestamp with time zone format xmlforest with object type
            341754
            Thank you very much Odie.
            You are right, the object type is part of bigger design. we have many more similar objects.