Column Datatype For Xmltable Columns Is Mandatory But The SQL Reference Guide Does Not Say So
(Doc ID 2005377.1)
Last updated on MARCH 28, 2019
Applies to:Oracle Database - Enterprise Edition - Version 22.214.171.124 to 126.96.36.199 [Release 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
<Bug 19780980> has been closed as not a bug stating that xmltable column's datatype has to be specified,
and whenever this is not done the query containing the XMLTABLE function can raise the error ORA-600 [QCTCHR : BFL].
However there is contradictory information in the Oracle Documentation related to the optionality of the datatype in XMLTABLE's COLUMN argument:
1. In the "XML DB Developer's Guide", topic “Using XQuery with Oracle XML DB” -> "Figure 5-2 XMLTABLE Syntax" (URL http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#ADXDB1700)
the XMLTABLE usage with datatypes is mandatory, as per this sentence:
"For each resulting column except the FOR ORDINALITY column, you must specify the column data type, which can be XMLType or any other SQL data type (called datatype in the syntax description)."
2. In the definition of "XMLTABLE" in "Database SQL Language Reference" (URL http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions240.htm) the XMLTABLE usage with datatypes is optional,
as per this sentence:
"The datatype is required unless XMLTable is used with XML schema-based storage of XMLType, datatype. In this case, if you omit datatype, Oracle XML DB infers the datatype from the XML schema. If the database is unable to determine the proper type for a node, then a default type of VARCHAR2(4000) is used."
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document