Column Datatype For Xmltable Columns Is Mandatory But The SQL Reference Guide Does Not Say So
Last updated on FEBRUARY 08, 2017
Applies to:Oracle Database - Enterprise Edition - Version 22.214.171.124 to 126.96.36.199 [Release 11.2]
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."
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms