My Oracle Support Banner

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 to [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
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 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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.