OraFAQ Forum: SQL & PL/SQL » ORA-01830 in XMLTABLE for DATE conversion

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01830 in XMLTABLE for DATE conversion (11g and 12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01830 in XMLTABLE for DATE conversion [message #663882]

Thu, 22 June 2017 13:20

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

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

Michel Cadot
Messages: 68758
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

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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

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

Michel Cadot
Messages: 68758
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

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

Michel Cadot
Messages: 68758
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

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

Michel Cadot
Messages: 68758
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

hamsterbacke
Messages: 5
Registered: June 2017

Junior Member

These "German" settings:
/forum/fa/13564/0/

/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

Attachment: nls_date_error.gif
(Size: 56.18KB, Downloaded 4513 times)

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

Michel Cadot
Messages: 68758
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

_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:

Code: [Select all] [Show/ hide]

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 '.');

Code: [Select all] [Show/ hide]

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

Michel Cadot
Messages: 68758
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:

Code: [Select all] [Show/ hide]

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

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:

Code: [Select all] [Show/ hide]

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

Michel Cadot
Messages: 68758
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

_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

Michel Cadot
Messages: 68758
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 11 20:44:28 CDT 2025