Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01830 in XMLTABLE for DATE conversion (11g and 12c)
ORA-01830 in XMLTABLE for DATE conversion [message #663882] Thu, 22 June 2017 13:20 Go to next message
hamsterbacke
Messages: 5
Registered: June 2017
Junior Member
Hi all,

with the following setting I receive an ORA-01830 ("date format picture ends before converting entire input string" / "Datumsformatstruktur endet vor Umwandlung der gesamten Eingabezeichenfolge") for converting GEBURTSDATUM (given in German notation):

CREATE TABLE clobs
( MY_XML CLOB,
ID NUMBER
)

Insert into clobs (ID, MY_XML) values (1, '<RESULT><GEBURTSDATUM_AM>1953-06-07</GEBURTSDATUM_AM><GEBURTSDATUM>07.06.1953</GEBURTSDATUM></RESULT>');

SELECT *
FROM XMLTABLE(
'/RESULT'
PASSING XMLTYPE((SELECT my_xml FROM clobs WHERE ID=1))
COLUMNS
GEBURTSDATUM_AM DATE PATH 'GEBURTSDATUM_AM'
,GEBURTSDATUM DATE PATH 'GEBURTSDATUM'
);


However on 1 of 3 available Oracle databases (11g/12c) this error does not occur and we can't determine why.

There is the following workaround but it's rather ugly and maybe affecting performance:

SELECT *
FROM XMLTABLE(
'/RESULT'
PASSING XMLTYPE((SELECT my_xml FROM clobs WHERE ID=1))
COLUMNS
GEBURTSDATUM_AM DATE PATH 'GEBURTSDATUM_AM'
,GEBURTSDATUM DATE PATH 'replace(GEBURTSDATUM,"([0-9]{2})\.([0-9]{2})\.([0-9]{4})","$3-$2-$1")'
);

Does anybody have a clue how to set environment variables in order to have an efficient and "beautiful" conversion to DATE?
The NLS_... parameters only seem to affect the format of the output.

Best Regards,
Simon

Report message to a moderator

Re: ORA-01830 in XMLTABLE for DATE conversion [message #663883 is a reply to message #663882] Thu, 22 June 2017 13:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: ORA-01830 in XMLTABLE for DATE conversion [message #663884 is a reply to message #663882] Thu, 22 June 2017 14:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code and align the columns in result.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

1/ NLS parameters set the default formats
2/ You should NEVER rely on NLS settings in your code. You should write your code in a way that it will work whatever is the end user settings.

Report message to a moderator

Re: ORA-01830 in XMLTABLE for DATE conversion [message #663888 is a reply to message #663882] Fri, 23 June 2017 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
 

SQL> SELECT *
  2  FROM XMLTABLE(
  3  '/RESULT'
  4  PASSING XMLTYPE((SELECT my_xml FROM clobs WHERE ID=1))
  5  COLUMNS
  6  GEBURTSDATUM_AM DATE PATH 'GEBURTSDATUM_AM'
  7  ,GEBURTSDATUM DATE PATH 'GEBURTSDATUM'
  8  );
SELECT *
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
To not rely on NLS settings, change your code to match your data:
SQL> SELECT to_date(GEBURTSDATUM_AM, 'YYYY-MM-DD') GEBURTSDATUM_AM,
  2         to_date(GEBURTSDATUM, 'DD.MM.YYYY') GEBURTSDATUM
  3  FROM XMLTABLE('/RESULT'
  4                PASSING XMLTYPE((SELECT my_xml FROM clobs WHERE ID=1))
  5                COLUMNS
  6                  GEBURTSDATUM_AM VARCHAR2(15) PATH 'GEBURTSDATUM_AM'
  7                 ,GEBURTSDATUM VARCHAR2(15) PATH 'GEBURTSDATUM'
  8               );
GEBURTSDATUM_AM     GEBURTSDATUM
------------------- -------------------
07/06/1953 00:00:00 07/06/1953 00:00:00

Report message to a moderator

Re: ORA-01830 in XMLTABLE for DATE conversion [message #663908 is a reply to message #663888] Fri, 23 June 2017 12:15 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
Well, relying on default NLS date format is certainly a bad idea but here we have a different situation. XML date format, as far as I know, is predefined as YYYY-MM-DD. It is Oracle who isn't honoring this XML standard (a known issue).

SY.

Report message to a moderator

Re: ORA-01830 in XMLTABLE for DATE conversion [message #663909 is a reply to message #663908] Fri, 23 June 2017 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Unless you have a XML Schema Definition with a type definition for an element, it is a string.
But you are right, if there would be such a definition Oracle will then not recognize it as it chose to use the NLS settings like in all other places in its SQL.

In the end, you can see that in OP's XML document, dates have different formats.

Report message to a moderator

Re: ORA-01830 in XMLTABLE for DATE conversion [message #663924 is a reply to message #663909] Mon, 26 June 2017 05:21 Go to previous messageGo to next message
hamsterbacke
Messages: 5
Registered: June 2017
Junior Member
Thank you for your replies!

My XML example was modified for this forum.
In the original use case only DD.MM.YYYY format occurs in the XML documents. And there are lot of those columns/elements...

Even if it's the wrong XML date format there is a configuration under which Oracle can parse it. And that's what I am looking for.
Until then I'll have to use one of the two workarounds posted here.

Report message to a moderator

Re: ORA-01830 in XMLTABLE for DATE conversion [message #663925 is a reply to message #663924] Mon, 26 June 2017 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
there is a configuration under which Oracle can parse it.
Which one?

Report message to a moderator

Re: ORA-01830 in XMLTABLE for DATE conversion [message #663937 is a reply to message #663925] Tue, 27 June 2017 01:59 Go to previous messageGo to next message
hamsterbacke
Messages: 5
Registered: June 2017
Junior Member
This one works:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

(with German NLS settings)

[Updated on: Tue, 27 June 2017 02:00]

Report message to a moderator

Re: ORA-01830 in XMLTABLE for DATE conversion [message #663938 is a reply to message #663937] Tue, 27 June 2017 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

What German settings mean?
Note this is not a question of Oracle version, I used this specific version to show it does not work. This is true (it does not work) for all Oracle versions supporting XML type.

Report message to a moderator

Re: ORA-01830 in XMLTABLE for DATE conversion [message #663942 is a reply to message #663938] Tue, 27 June 2017 06:27 Go to previous messageGo to next message
hamsterbacke
Messages: 5
Registered: June 2017
Junior Member
These "German" settings:
/forum/fa/13564/0/


But I've tried those settings with the following database and it did not work:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE 11.2.0.2.0 Production"
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Report message to a moderator

Re: ORA-01830 in XMLTABLE for DATE conversion [message #663949 is a reply to message #663942] Tue, 27 June 2017 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

This has nothing to do with Oracle version, this has to do with your NLS_DATE_FORMAT setting and I advise you read again my first answer.
Whatever you set, I, as the client, can set my NLS parameters as I want and your code will no more "work".

Report message to a moderator

Re: ORA-01830 in XMLTABLE for DATE conversion [message #663950 is a reply to message #663942] Tue, 27 June 2017 10:32 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
You did understand, that this has nothing to do with your ORACLE NLS_DATE_FORMAT, didn't you ?!

Then you could try something like:

SELECT nr, tag, datxt, 
  CASE WHEN tag='GEBURTSDATUM_AM' 
       THEN to_date(datxt,'YYYY-MM-DD')
       ELSE to_date(datxt,'DD.MM.YYYY') 
        END datum  
  FROM xmltable('/RESULT/GEBURTSDATUM_AM | /RESULT/GEBURTSDATUM'
       PASSING XMLTYPE('<RESULT><GEBURTSDATUM_AM>1953-06-07</GEBURTSDATUM_AM><GEBURTSDATUM>07.06.1953</GEBURTSDATUM></RESULT>') 
   COLUMNS
            nr    FOR ORDINALITY,
            tag   VARCHAR2(100) PATH 'local-name()',
            datxt VARCHAR2(100) PATH '.');     


NR      TAG             DATXT           DATUM
---------------------------------------------------
1       GEBURTSDATUM_AM 1953-06-07      07.06.1953
2       GEBURTSDATUM    07.06.1953      07.06.1953

[Updated on: Tue, 27 June 2017 10:39]

Report message to a moderator

Re: ORA-01830 in XMLTABLE for DATE conversion [message #663951 is a reply to message #663950] Tue, 27 June 2017 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Er, this is not what is asked (implicitly by the given query which returns 1 line with 2 date columns), I don't see what you wanted to show and I don't see the advantage compare to the version I posted.
By the way, for me the result is:
SQL> SELECT nr, tag, datxt,
  2    CASE WHEN tag='GEBURTSDATUM_AM'
  3         THEN to_date(datxt,'YYYY-MM-DD')
  4         ELSE to_date(datxt,'DD.MM.YYYY')
  5          END datum
  6    FROM xmltable('/RESULT/GEBURTSDATUM_AM | /RESULT/GEBURTSDATUM'
  7         PASSING XMLTYPE('<RESULT><GEBURTSDATUM_AM>1953-06-07</GEBURTSDATUM_AM><GEBURTSDATUM>07.06.1953</GEBURTSDATUM></RESULT>')
  8     COLUMNS
  9              nr    FOR ORDINALITY,
 10              tag   VARCHAR2(100) PATH 'local-name()',
 11              datxt VARCHAR2(100) PATH '.');
        NR TAG             DATXT        DATUM
---------- --------------- ------------ -------------------
         1 GEBURTSDATUM_AM 1953-06-07   07/06/1953 00:00:00
         2 GEBURTSDATUM    07.06.1953   07/06/1953 00:00:00

Report message to a moderator

Re: ORA-01830 in XMLTABLE for DATE conversion [message #663952 is a reply to message #663949] Tue, 27 June 2017 16:16 Go to previous messageGo to next message
hamsterbacke
Messages: 5
Registered: June 2017
Junior Member
If the NLS_DATE_FORMAT was the solution, I'd love to set it explicitly just before the SELECT statement:

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY';

Session altered.

SQL> SELECT *
  2  FROM xmltable('/RESULT'
  3         PASSING XMLTYPE('<RESULT><GEBURTSDATUM>07.06.1953</GEBURTSDATUM></RESULT>')
  4     COLUMNS
  5      GEBURTSDATUM DATE PATH 'GEBURTSDATUM'
  6     );
SELECT *
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

But no matter how I set NLS_DATE_FORMAT for the session I can't get rid of this error.

If the Oracle version does not matter, what else could have an influence?

Report message to a moderator

Re: ORA-01830 in XMLTABLE for DATE conversion [message #663957 is a reply to message #663952] Wed, 28 June 2017 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
If the Oracle version does not matter, what else could have an influence?
Bugs.

Report message to a moderator

Re: ORA-01830 in XMLTABLE for DATE conversion [message #663958 is a reply to message #663952] Wed, 28 June 2017 01:09 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Quote:
But no matter how I set NLS_DATE_FORMAT for the session I can't get rid of this error.
The NLS_DATE_FORMAT has no influence how ORACLE will accept a date in your XML, it has to be 'YYYY-MM-DD'!

Michel, excuse, my last message was addressed to hamsterbacke not to you.

[Updated on: Wed, 28 June 2017 01:13]

Report message to a moderator

Re: ORA-01830 in XMLTABLE for DATE conversion [message #663960 is a reply to message #663958] Wed, 28 June 2017 01:29 Go to previous message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
The NLS_DATE_FORMAT has no influence how ORACLE will accept a date in your XML, it has to be 'YYYY-MM-DD'!
Yes, and this is the problem, it depends on your version, patchset and even PSU.
Some use NLS, some use XML standard but none accept different date formats in the XML at the same time.

Report message to a moderator

Previous Topic: not gettting output for showplanfrpspreadsheetcode11g
Next Topic: How to generate JSON
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri May 23 14:42:45 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.