ORA-01706 with XMLTABLE and basing a VARCHAR2(4000) column on an XPath substring function
Last updated on APRIL 05, 2018
Applies to:Oracle Database - Enterprise Edition - Version 126.96.36.199 and later
Information in this document applies to any platform.
A SQL query references an XML object in a CLOB column of a table and one of the columns in the XMLTABLE definition is MESSAGE_TEXT varchar2(4000) PATH 'substring(msgText,1,4000)'. If the msgText value is in excess of 4000 characters, the SQL statement fails with ORA-01706.
FROM test_clob x, XMLTABLE('/chatTranscript/message'
MESSAGE_TEXT varchar2(4000) PATH 'substring(msgText,1,4000)')
mSQL> SQL> 2 3 4 5
ORA-01706: user function result value was too large
This statement should not fail as the substring is taking the first 4000 characters.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms