2 Replies Latest reply: Aug 20, 2013 4:04 PM by siromega RSS

    XMLTable default values for timestamp results in ORA-01843: not a valid month

    siromega

      When I try to provide a default for a timestamp value in the XMLTABLE function, I am greeted with an error - ORA-01843: not a valid month - no matter how I provide that default value. Whether there is a value present in the XML or not is irrelavant for this bug to occur. It appears to be an incomplete fix of bug number 9745897 (thread).

       

      select x.*

      from

      xmltable('/DOC' passing xmltype('<DOC><DT>2013-08-14T15:08:31</DT></DOC>')

        COLUMNS dt timestamp default sysdate) x;

       

      select x.*

      from

      xmltable('/DOC' passing xmltype('<DOC><DT>2013-08-14T15:08:31</DT></DOC>')

        COLUMNS dt timestamp default systimestamp) x;

       

      select x.*

      from

      xmltable('/DOC' passing xmltype('<DOC><DT>2013-08-14T15:08:31</DT></DOC>')

        COLUMNS dt timestamp default to_char(systimestamp, 'YYYY-MM-DD"T"HH24:MI:SS') ) x;

       

      Edit: A little more followup.

       

      This works:

      select x.*

      from

      xmltable('/DOC' passing xmltype('<DOC></DOC>')

        COLUMNS dt date default sysdate) x;

       

      This also works, except for its just the date, and not the date/time

      select x.*

      from

      xmltable('/DOC' passing xmltype('<DOC></DOC>')

        COLUMNS dt timestamp default sysdate) x;

       

      This doesn't work

      select x.*

      from

      xmltable('/DOC' passing xmltype('<DOC></DOC>')

        COLUMNS dt timestamp default systimestamp) x;

      ORA-01861: literal does not match format string

        • 1. Re: XMLTable default values for timestamp results in ORA-01843: not a valid month
          odie_63

          Hi,

           

          First of all, let's check the manual for the DEFAULT clause :

          XMLTABLE SQL/XML Function in Oracle XML DB

          The optional DEFAULT clause specifies the value to use when the PATH expression results in an empty sequence (or NULL). Its expr is an XQuery expression that is evaluated to produce the default value.

          According to the documentation, the DEFAULT clause should specify an XQuery expression.

          However, that is wrong, the actual implementation only expects an expression that resolves to a string, the content is not interpreted.

          So, bottom line is if we don't directly specify a string, the expression will be implicitly converted to one, and we all know how bad things can go when implicit conversions occur, especially when dates or timestamps are involved.

           

          Now let's focus on how the DEFAULT clause affects the query evaluation.

          When a DEFAULT clause is specified, Oracle rewrites the projection differently and do not use the native xs:dateTime format to convert the value  :

          select x.*

          from

          xmltable('/DOC' passing xmltype('<DOC><DT>2013-08-14T15:08:31</DT></DOC>')

            COLUMNS dt timestamp default systimestamp

            ) x

          becomes :

          SELECT CASE EXISTSNODE(VALUE(KOKBF$),'/DOC/DT')

                   WHEN 1 THEN CAST(TO_TIMESTAMP(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/DOC/DT')),50,1,2)) AS timestamp )

                   ELSE CAST(TO_TIMESTAMP(TO_CHAR(SYSTIMESTAMP(6)),'SYYYY-MM-DD"T"HH24:MI:SSXFF') AS timestamp )

                 END  "DT"

          FROM TABLE("SYS"."XQSEQUENCE"(EXTRACT("SYS"."XMLTYPE"('<DOC><DT>2013-08-14T15:08:31</DT></DOC>'),'/DOC'))) "KOKBF$"

          See the red part : it doesn't use the format parameter, so the conversion relies on the session's NLS settings.

           

          When there's no DEFAULT clause, the TO_TIMESTAMP function uses an explicit format :

          select x.*

          from

          xmltable('/DOC' passing xmltype('<DOC><DT>2013-08-14T15:08:31</DT></DOC>')

            COLUMNS dt timestamp --default systimestamp

            ) x

          rewritten to :

          SELECT CAST(

                   TO_TIMESTAMP(

                     SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/DOC/DT'),0,0,20971520,0),50,1,2)

                   , 'SYYYY-MM-DD"T"HH24:MI:SSXFF'

                   )

                   AS timestamp --default systimestamp

                 ) "DT"

          FROM TABLE("SYS"."XQSEQUENCE"(EXTRACT("SYS"."XMLTYPE"('<DOC><DT>2013-08-14T15:08:31</DT></DOC>'),'/DOC'))) "KOKBF$"

          so yes, maybe there's a bug here.

           

          Edit: A little more followup.

           

          This works:

          select x.*

          from

          xmltable('/DOC' passing xmltype('<DOC></DOC>')

            COLUMNS dt date default sysdate) x;

          Actually no, it doesn't work. Granted, maybe it doesn't produce any error, but the result is incorrect.

          As explained, the conversion relies on the session NLS (NLS_DATE_FORMAT in this case) :

          SQL> show parameters nls_date_format

           

          NAME                                 TYPE        VALUE

          ------------------------------------ ----------- ------------------------------

          nls_date_format                      string      DD/MM/RR

          SQL>

          SQL> select sysdate from dual;

           

          SYSDATE

          --------

          16/08/13

           

          SQL> select x.*

            2  from

            3  xmltable('/DOC' passing xmltype('<DOC></DOC>')

            4    COLUMNS dt date default sysdate) x;

           

          DT

          --------

          13/08/16

           

           

          Oracle first converts SYSDATE to a string using current NLS_DATE_FORMAT, resulting in '16/08/13'

          Then this string is converted to a DATE using the xs:date format 'YYYY-MM-DD' resulting in 13/08/0016 (August 13, 0016) which is incorrect.

           

          The obvious workaround to this issue is to control how Oracle implicitly converts from string to date/timestamp format :

          SQL> alter session set NLS_TIMESTAMP_FORMAT= 'YYYY-MM-DD"T"HH24:MI:SS';

           

          Session altered.

           

          SQL> select x.*

            2  from

            3  xmltable('/DOC' passing xmltype('<DOC><DT>2013-08-14T15:08:31</DT></DOC>')

            4    COLUMNS dt timestamp default systimestamp

            5    ) x;

           

          DT

          ---------------------------------------------------------------------------

          2013-08-14T15:08:31

           

          SQL> select x.*

            2  from

            3  xmltable('/DOC' passing xmltype('<DOC></DOC>')

            4    COLUMNS dt timestamp default systimestamp) x;

            COLUMNS dt timestamp default systimestamp) x

                                         *

          ERROR at line 4:

          ORA-01861: literal does not match format string

           

           

          SQL> select x.*

            2  from

            3  xmltable('/DOC' passing xmltype('<DOC></DOC>')

            4    COLUMNS dt timestamp default cast(systimestamp as timestamp)) x;

           

          DT

          ---------------------------------------------------------------------------

          2013-08-16T12:32:58

           

          • 2. Re: XMLTable default values for timestamp results in ORA-01843: not a valid month
            siromega

            Yup, that is it. Thanks a ton!!

             

            So what I have to do is in my PL/SQL that uses this function, I have to get the current timestamp format, change it, do the work, and then change it back when I'm done (or exception).

             

            Now if I could only find out how to avoid core dumping the DB when I specify EMPTY_CLOB as a default value for a clob field.