5 Replies Latest reply: Jul 29, 2010 12:38 PM by Marco Gralike RSS

    Error with basic XMLTable

    783942
      Hi all,

      I'm struggling to get a basic XML table working, i've stripped everything back to basics but still no joy. This works:
      select xmltype(bfilename('XMLDIR','mylog.log'), nls_charset_id('AL32UTF8')) from dual
      So i know i can access the file and create a valid XMLType. However, when i try and create a table:
      select * from XMLTABLE( '/log/record'
      passing xmltype(bfilename('XMLDIR','mylog.log'), nls_charset_id('AL32UTF8'))
             columns
                         message varchar2(20) path 'message'); 
      or even
      select * from XMLTABLE( '/log/record'
      passing xmltype(bfilename('XMLDIR','mylog.log'), nls_charset_id('AL32UTF8'))
      );
      I get the error:

      ORA-29900: operator binding does not exist
      ORA-06540: PL/SQL: compilation error
      ORA-06553: PLS-907: cannot load library unit SYS.XQSEQUENCEFROMXMLTYPE (referenced by SYS.XQSEQUENCE)

      I've spent ages trying different things, playing with the namespace clause, different XQuery paths etc. Here's the XML i used with the code above to test:
      <?xml version="1.0" encoding="utf-8" ?>
      <log>
      <record>
        <date>2010-06-30T17:31:11</date>
        <millis>1277915471029</millis>
        <sequence>19</sequence>
        <logger>timings</logger>
        <level>FINE</level>
        <class>com.MASAgentRunner</class>
        <method>run</method>
        <thread>11</thread>
        <message>Agent 1370 started running</message>
      </record>
      <record>
        <date>2010-06-30T17:31:11</date>
        <millis>1277915471029</millis>
        <sequence>19</sequence>
        <logger>timings</logger>
        <level>FINE</level>
        <class>com.MASAgentRunner</class>
        <method>run</method>
        <thread>11</thread>
        <message>Agent 345321515</message>
      </record>
      <record>
        <date>2010-06-30T17:31:11</date>
        <millis>1277915471029</millis>
        <sequence>19</sequence>
        <logger>timings</logger>
        <level>FINE</level>
        <class>com.MASAgentRunner</class>
        <method>run</method>
        <thread>11</thread>
        <message>Agent complete</message>
      </record>
      </log>
      Thanks
      Alistair
        • 1. Re: Error with basic XMLTable
          odie_63
          Hi,

          First thing first, what's your db version? Please give all four digits.

          NB : XQuery support is available for 10.2 and upwards.
          • 2. Re: Error with basic XMLTable
            Marco Gralike
            It should work, although substitution of the whole bfilename stuff into the PASSING clause, which should be an expression, where "expr" (XMLTABLE: http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions251.htm#SQLRF06232) is defined in 11.2 manuals as:
            The expr in the XML_passing_clause is an expression returning an XMLType or an instance of a SQL scalar data type that is used as the context for evaluating the XQuery expression.
            You can specify only one expr in the PASSING clause without an identifier. The result of evaluating each expr is bound to the corresponding identifier in the XQuery_string.
            If any expr that is not followed by an AS clause, then the result of evaluating that expression is used as the context item for evaluating the XQuery_string.
            The following works on 11.2., BUT bear in mind that this can become VERY painful regarding performance (I/O, PGA and CPU resources) as the physical reads AND "COLLECTION ITERATOR PICKLER FETCH" suggests
            [oracle@localhost ~]$ sqlplus / as sysdba
            
            SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 29 09:20:24 2010
            
            Copyright (c) 1982, 2009, Oracle.  All rights reserved.
            
            
            Connected to:
            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options
            
            SQL> create user otn identified by otn account unlock;
            
            User created.
            
            SQL> grant dba to otn;
            
            Grant succeeded.
            
            SQL> conn otn/otn
            Connected.
            
            SQL> set pages 5000
            SQL> set lines 1000
            SQL> set long 10000
            
            SQL> select xmltype(cursor(select owner, schema_url, local from all_xml_schemas order by owner)) from dual;
            
            XMLTYPE(CURSOR(SELECTOWNER,SCHEMA_URL,LOCALFROMALL_XML_SCHEMASORDERBYOWNER))
            -----------------------------------------------------------------------------------------------------------
            <?xml version="1.0"?>
            <ROWSET>
              <ROW>
                <OWNER>EXFSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/rlmgr/rclsprop.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>EXFSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/rlmgr/rulecond.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>MDSYS</OWNER>
                <SCHEMA_URL>http://www.opengis.net/gml/feature.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>MDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/spatial/georaster/georaster.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>MDSYS</OWNER>
                <SCHEMA_URL>http://www.w3.org/1999/xlink/xlinks.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>MDSYS</OWNER>
                <SCHEMA_URL>http://www.opengis.net/gml/geometry.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>ORDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/ord/dicom/preference_1_0</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>ORDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/ord/dicom/mapping_1_0</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>ORDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/ord/dicom/metadata_1_0</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>ORDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/ord/dicom/constraint_1_0</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>ORDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/ord/dicom/anonymity_1_0</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>ORDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/ord/meta/xmp</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>ORDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/ord/dicom/standardDictionary_1_0</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>ORDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/ord/meta/iptc</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>ORDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/ord/dicom/mddatatype_1_0</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>ORDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/ord/dicom/orddicom_1_0</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>ORDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/ord/dicom/datatype_1_0</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>ORDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/ord/dicom/privateDictionary_1_0</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>ORDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/ord/meta/exif</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>ORDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>ORDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/ord/meta/ordimage</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>ORDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/ord/dicom/UIDdefinition_1_0</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>ORDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/ord/dicom/manifest_1_0</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>ORDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/ord/dicom/attributeTag_1_0</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>ORDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/ord/meta/dicomImage</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>SYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/streams/schemas/lcr/streamslcr.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/xdb/csx.xmltr.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://www.w3.org/2001/csx.xml.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/xdb/XDBResource.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/xdb/XDBSchema.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/xs/principal.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/xs/aclids.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/xs/dataSecurity.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/xdb/xdbconfig.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/xs/securityclass.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/xs/roleset.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/xdb/stats.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://www.w3.org/2001/csx.XInclude.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://www.w3.org/2001/XInclude.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://www.w3.org/1999/csx.xlink.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://www.w3.org/1999/xlink.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/xdb/XDBFolderListing.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/xdb/xmltr.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://www.w3.org/2001/xml.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/xdb/log/httplog.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/xdb/log/ftplog.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/xdb/log/xdblog.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/xdb/XDBStandard.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/xdb/XDBResConfig.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/xdb/acl.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/xdb/dav.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
            </ROWSET>
            
            SQL> declare
              2     rc sys_refcursor;
              3  begin
              4     open rc FOR SELECT * FROM (select owner, schema_url, local from all_xml_schemas order by owner);
              5  dbms_xslprocessor.clob2file(xmltype(rc).getClobVal(),'TMPDIR','otn_dev_xsd_schema.xml');
              6  end;
              7 ;
            
            PL/SQL procedure successfully completed.
            
            SQL> commit;
            
            commit complete
            
            SQL> ! ls -ltra /tmp/*.xml
            
            -rw-rw-r-- 1 oracle oracle 6563 Jul 29 09:36 /tmp/otn_dev_xsd_schema.xml
            
            SQL> select xmltype(bfilename('TMPDIR','otn_dev_xsd_schema.xml'), nls_charset_id('AL32UTF8')) from dual;
            
            XMLTYPE(BFILENAME('TMPDIR','OTN_DEV_XSD_SCHEMA.XML'),NLS_CHARSET_ID('AL32UTF8'))
            ---------------------------------------------------------------------------------------
            <?xml version="1.0"?>
            <ROWSET>
              <ROW>
                <OWNER>EXFSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/rlmgr/rclsprop.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>EXFSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/rlmgr/rulecond.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>MDSYS</OWNER>
                <SCHEMA_URL>http://www.opengis.net/gml/feature.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>MDSYS</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/spatial/georaster/georaster.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>MDSYS</OWNER>
                <SCHEMA_URL>http://www.w3.org/1999/xlink/xlinks.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              <ROW>
                <OWNER>MDSYS</OWNER>
                <SCHEMA_URL>http://www.opengis.net/gml/geometry.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
              etc
              etc
              etc
              etc
              etc
              <ROW>
                   <OWNER>XDB</OWNER>
                <SCHEMA_URL>http://xmlns.oracle.com/xdb/dav.xsd</SCHEMA_URL>
                <LOCAL>NO</LOCAL>
              </ROW>
            </ROWSET>
            
            51 rows selected.
            Edited by: Marco Gralike on Jul 29, 2010 7:19 PM
            • 3. Re: Error with basic XMLTable
              Marco Gralike
              SQL> SELECT xtab.owner
                2         , xtab.schema_url
                3         , xtab.local
                4  FROM (select xmltype(bfilename('TMPDIR','otn_dev_xsd_schema.xml'), nls_charset_id('AL32UTF8')) OBJECT_VALUE from dual) xfile
                5        , XMLTABLE ('/ROWSET/ROW'
                6        PASSING xfile.object_value
                7        COLUMNS OWNER      VARCHAR2(30) path 'OWNER'
                8              , SCHEMA_URL VARCHAR2(80) path 'SCHEMA_URL'
                9              , LOCAL      VARCHAR2(3)  path 'LOCAL'
               10        ) xtab
               11  ;
              
              OWNER                          SCHEMA_URL                                                                       LOC
              ------------------------------ -------------------------------------------------------------------------------- ---
              EXFSYS                         http://xmlns.oracle.com/rlmgr/rclsprop.xsd                                       NO
              EXFSYS                         http://xmlns.oracle.com/rlmgr/rulecond.xsd                                       NO
              MDSYS                          http://www.opengis.net/gml/feature.xsd                                           NO
              MDSYS                          http://xmlns.oracle.com/spatial/georaster/georaster.xsd                          NO
              MDSYS                          http://www.w3.org/1999/xlink/xlinks.xsd                                          NO
              MDSYS                          http://www.opengis.net/gml/geometry.xsd                                          NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/preference_1_0                                 NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/mapping_1_0                                    NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/metadata_1_0                                   NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/constraint_1_0                                 NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/anonymity_1_0                                  NO
              ORDSYS                         http://xmlns.oracle.com/ord/meta/xmp                                             NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/standardDictionary_1_0                         NO
              ORDSYS                         http://xmlns.oracle.com/ord/meta/iptc                                            NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/mddatatype_1_0                                 NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/orddicom_1_0                                   NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/datatype_1_0                                   NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/privateDictionary_1_0                          NO
              ORDSYS                         http://xmlns.oracle.com/ord/meta/exif                                            NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0                                 NO
              ORDSYS                         http://xmlns.oracle.com/ord/meta/ordimage                                        NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/UIDdefinition_1_0                              NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/manifest_1_0                                   NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/attributeTag_1_0                               NO
              ORDSYS                         http://xmlns.oracle.com/ord/meta/dicomImage                                      NO
              SYS                            http://xmlns.oracle.com/streams/schemas/lcr/streamslcr.xsd                       NO
              XDB                            http://xmlns.oracle.com/xdb/csx.xmltr.xsd                                        NO
              XDB                            http://www.w3.org/2001/csx.xml.xsd                                               NO
              XDB                            http://xmlns.oracle.com/xdb/XDBResource.xsd                                      NO
              XDB                            http://xmlns.oracle.com/xdb/XDBSchema.xsd                                        NO
              XDB                            http://xmlns.oracle.com/xs/principal.xsd                                         NO
              XDB                            http://xmlns.oracle.com/xs/aclids.xsd                                            NO
              XDB                            http://xmlns.oracle.com/xs/dataSecurity.xsd                                      NO
              XDB                            http://xmlns.oracle.com/xdb/xdbconfig.xsd                                        NO
              XDB                            http://xmlns.oracle.com/xs/securityclass.xsd                                     NO
              XDB                            http://xmlns.oracle.com/xs/roleset.xsd                                           NO
              XDB                            http://xmlns.oracle.com/xdb/stats.xsd                                            NO
              XDB                            http://www.w3.org/2001/csx.XInclude.xsd                                          NO
              XDB                            http://www.w3.org/2001/XInclude.xsd                                              NO
              XDB                            http://www.w3.org/1999/csx.xlink.xsd                                             NO
              XDB                            http://www.w3.org/1999/xlink.xsd                                                 NO
              XDB                            http://xmlns.oracle.com/xdb/XDBFolderListing.xsd                                 NO
              XDB                            http://xmlns.oracle.com/xdb/xmltr.xsd                                            NO
              XDB                            http://www.w3.org/2001/xml.xsd                                                   NO
              XDB                            http://xmlns.oracle.com/xdb/log/httplog.xsd                                      NO
              XDB                            http://xmlns.oracle.com/xdb/log/ftplog.xsd                                       NO
              XDB                            http://xmlns.oracle.com/xdb/log/xdblog.xsd                                       NO
              XDB                            http://xmlns.oracle.com/xdb/XDBStandard.xsd                                      NO
              XDB                            http://xmlns.oracle.com/xdb/XDBResConfig.xsd                                     NO
              XDB                            http://xmlns.oracle.com/xdb/acl.xsd                                              NO
              XDB                            http://xmlns.oracle.com/xdb/dav.xsd                                              NO
              
              51 rows selected.
              
              SQL> CREATE or REPLACE view MY_EXTERNAL_XFILE
                2  ( OWNER
                3  , SCHEMA
                4  , MINE
                5  )
                6  AS
                7  SELECT xtab.owner
                8       , xtab.schema_url
                9       , xtab.local
               10  FROM (select xmltype(bfilename('TMPDIR','otn_dev_xsd_schema.xml'), nls_charset_id('AL32UTF8')) OBJECT_VALUE from dual) xfile
               11     , XMLTABLE ('/ROWSET/ROW'
               12                 PASSING xfile.object_value
               13                 COLUMNS OWNER      VARCHAR2(30) path 'OWNER'
               14                       , SCHEMA_URL VARCHAR2(80) path 'SCHEMA_URL'
               15                       , LOCAL      VARCHAR2(3)  path 'LOCAL'
               16                 ) xtab
               17  ;
              
              View created.
              
              SQL> set lines 115
              
              SQL> desc MY_EXTERNAL_XFILE
               Name                                                           Null?    Type
               -------------------------------------------------------------- -------- ------------------------------------------
               OWNER                                                                   VARCHAR2(30)
               SCHEMA                                                                  VARCHAR2(80)
               MINE                                                                    VARCHAR2(3)
              
              SQL> select * from MY_EXTERNAL_XFILE
                2  ;
              
              OWNER                          SCHEMA                                                                           MIN
              ------------------------------ -------------------------------------------------------------------------------- ---
              EXFSYS                         http://xmlns.oracle.com/rlmgr/rclsprop.xsd                                       NO
              EXFSYS                         http://xmlns.oracle.com/rlmgr/rulecond.xsd                                       NO
              MDSYS                          http://www.opengis.net/gml/feature.xsd                                           NO
              MDSYS                          http://xmlns.oracle.com/spatial/georaster/georaster.xsd                          NO
              MDSYS                          http://www.w3.org/1999/xlink/xlinks.xsd                                          NO
              MDSYS                          http://www.opengis.net/gml/geometry.xsd                                          NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/preference_1_0                                 NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/mapping_1_0                                    NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/metadata_1_0                                   NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/constraint_1_0                                 NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/anonymity_1_0                                  NO
              ORDSYS                         http://xmlns.oracle.com/ord/meta/xmp                                             NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/standardDictionary_1_0                         NO
              ORDSYS                         http://xmlns.oracle.com/ord/meta/iptc                                            NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/mddatatype_1_0                                 NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/orddicom_1_0                                   NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/datatype_1_0                                   NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/privateDictionary_1_0                          NO
              ORDSYS                         http://xmlns.oracle.com/ord/meta/exif                                            NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0                                 NO
              ORDSYS                         http://xmlns.oracle.com/ord/meta/ordimage                                        NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/UIDdefinition_1_0                              NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/manifest_1_0                                   NO
              ORDSYS                         http://xmlns.oracle.com/ord/dicom/attributeTag_1_0                               NO
              ORDSYS                         http://xmlns.oracle.com/ord/meta/dicomImage                                      NO
              SYS                            http://xmlns.oracle.com/streams/schemas/lcr/streamslcr.xsd                       NO
              XDB                            http://xmlns.oracle.com/xdb/csx.xmltr.xsd                                        NO
              XDB                            http://www.w3.org/2001/csx.xml.xsd                                               NO
              XDB                            http://xmlns.oracle.com/xdb/XDBResource.xsd                                      NO
              XDB                            http://xmlns.oracle.com/xdb/XDBSchema.xsd                                        NO
              XDB                            http://xmlns.oracle.com/xs/principal.xsd                                         NO
              XDB                            http://xmlns.oracle.com/xs/aclids.xsd                                            NO
              XDB                            http://xmlns.oracle.com/xs/dataSecurity.xsd                                      NO
              XDB                            http://xmlns.oracle.com/xdb/xdbconfig.xsd                                        NO
              XDB                            http://xmlns.oracle.com/xs/securityclass.xsd                                     NO
              XDB                            http://xmlns.oracle.com/xs/roleset.xsd                                           NO
              XDB                            http://xmlns.oracle.com/xdb/stats.xsd                                            NO
              XDB                            http://www.w3.org/2001/csx.XInclude.xsd                                          NO
              XDB                            http://www.w3.org/2001/XInclude.xsd                                              NO
              XDB                            http://www.w3.org/1999/csx.xlink.xsd                                             NO
              XDB                            http://www.w3.org/1999/xlink.xsd                                                 NO
              XDB                            http://xmlns.oracle.com/xdb/XDBFolderListing.xsd                                 NO
              XDB                            http://xmlns.oracle.com/xdb/xmltr.xsd                                            NO
              XDB                            http://www.w3.org/2001/xml.xsd                                                   NO
              XDB                            http://xmlns.oracle.com/xdb/log/httplog.xsd                                      NO
              XDB                            http://xmlns.oracle.com/xdb/log/ftplog.xsd                                       NO
              XDB                            http://xmlns.oracle.com/xdb/log/xdblog.xsd                                       NO
              XDB                            http://xmlns.oracle.com/xdb/XDBStandard.xsd                                      NO
              XDB                            http://xmlns.oracle.com/xdb/XDBResConfig.xsd                                     NO
              XDB                            http://xmlns.oracle.com/xdb/acl.xsd                                              NO
              XDB                            http://xmlns.oracle.com/xdb/dav.xsd                                              NO
              
              51 rows selected.
              
              
              SQL> select * from MY_EXTERNAL_XFILE where owner='XDB'
                2  and instr(SCHEMA,'w3') > 0
                3  ;
              
              OWNER                          SCHEMA                                                                           MIN
              ------------------------------ -------------------------------------------------------------------------------- ---
              XDB                            http://www.w3.org/2001/csx.xml.xsd                                               NO
              XDB                            http://www.w3.org/2001/csx.XInclude.xsd                                          NO
              XDB                            http://www.w3.org/2001/XInclude.xsd                                              NO
              XDB                            http://www.w3.org/1999/csx.xlink.xsd                                             NO
              XDB                            http://www.w3.org/1999/xlink.xsd                                                 NO
              XDB                            http://www.w3.org/2001/xml.xsd                                                   NO
              
              6 rows selected.
              
              SQL> select * from MY_EXTERNAL_XFILE where owner='MDSYS'
                2  and instr(SCHEMA,'w3') > 0
                3  ;
              
              OWNER                          SCHEMA                                                                           MIN
              ------------------------------ -------------------------------------------------------------------------------- ---
              MDSYS                          http://www.w3.org/1999/xlink/xlinks.xsd                                          NO
              
              
              Execution Plan
              ----------------------------------------------------------
              Plan hash value: 2888251253
              
              -------------------------------------------------------------------------------------------------------------
              | Id  | Operation                          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
              -------------------------------------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT                   |                        |     4 |     8 |    44  (30)| 00:00:01 |
              |   1 |  NESTED LOOPS                      |                        |     4 |     8 |    44  (30)| 00:00:01 |
              |   2 |   FAST DUAL                        |                        |     1 |       |     2   (0)| 00:00:01 |
              |*  3 |   COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |     4 |     8 |    42  (31)| 00:00:01 |
              -------------------------------------------------------------------------------------------------------------
              
              Predicate Information (identified by operation id):
              ---------------------------------------------------
              
                 3 - filter(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/OWNER'),0,0,209715
                            20,0),50,1,2) AS VARCHAR2(30) )='MDSYS' AND INSTR(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VA
                            LUE(KOKBF$),'/ROW/SCHEMA_URL'),0,0,20971520,0),50,1,2) AS VARCHAR2(80) ),'w3')>0)
              
              
              Statistics
              ----------------------------------------------------------
                       19  recursive calls
                        0  db block gets
                      462  consistent gets
                        0  physical reads
                        0  redo size
                      583  bytes sent via SQL*Net to client
                      419  bytes received via SQL*Net from client
                        2  SQL*Net roundtrips to/from client
                        0  sorts (memory)
                        0  sorts (disk)
                        1  rows processed
              • 4. Re: Error with basic XMLTable
                Marco Gralike
                SQL> conn / as sysdba
                Connected.
                
                SQL> startup force
                ORACLE instance started.
                
                Total System Global Area  313860096 bytes
                Fixed Size                  1336232 bytes
                Variable Size             251661400 bytes
                Database Buffers           54525952 bytes
                Redo Buffers                6336512 bytes
                Database mounted.
                Database opened.
                
                SQL>  conn otn/otn
                Connected.
                
                SQL> set autotrace on
                
                SQL> select * from MY_EXTERNAL_XFILE where owner='MDSYS'
                  2  and instr(SCHEMA,'w3') > 0;
                
                OWNER                          SCHEMA                                                                           MIN
                ------------------------------ -------------------------------------------------------------------------------- ---
                MDSYS                          http://www.w3.org/1999/xlink/xlinks.xsd                                          NO
                
                
                Execution Plan
                ----------------------------------------------------------
                Plan hash value: 2888251253
                
                -------------------------------------------------------------------------------------------------------------
                | Id  | Operation                          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
                -------------------------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT                   |                        |     4 |     8 |    44  (30)| 00:00:01 |
                |   1 |  NESTED LOOPS                      |                        |     4 |     8 |    44  (30)| 00:00:01 |
                |   2 |   FAST DUAL                        |                        |     1 |       |     2   (0)| 00:00:01 |
                |*  3 |   COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |     4 |     8 |    42  (31)| 00:00:01 |
                -------------------------------------------------------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                
                   3 - filter(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/OWNER'),0,0,209715
                              20,0),50,1,2) AS VARCHAR2(30) )='MDSYS' AND INSTR(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VA
                              LUE(KOKBF$),'/ROW/SCHEMA_URL'),0,0,20971520,0),50,1,2) AS VARCHAR2(80) ),'w3')>0)
                
                
                Statistics
                ----------------------------------------------------------
                       3624  recursive calls
                          0  db block gets
                       2888  consistent gets
                        105  physical reads
                          0  redo size
                        583  bytes sent via SQL*Net to client
                        419  bytes received via SQL*Net from client
                          2  SQL*Net roundtrips to/from client
                        143  sorts (memory)
                          0  sorts (disk)
                          1  rows processed
                
                
                SQL> select * from MY_EXTERNAL_XFILE where owner='MDSYS'
                  2  and instr(SCHEMA,'w3') > 0;
                
                OWNER                          SCHEMA                                                                           MIN
                ------------------------------ -------------------------------------------------------------------------------- ---
                MDSYS                          http://www.w3.org/1999/xlink/xlinks.xsd                                          NO
                
                
                Execution Plan
                ----------------------------------------------------------
                Plan hash value: 2888251253
                
                -------------------------------------------------------------------------------------------------------------
                | Id  | Operation                          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
                -------------------------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT                   |                        |     4 |     8 |    44  (30)| 00:00:01 |
                |   1 |  NESTED LOOPS                      |                        |     4 |     8 |    44  (30)| 00:00:01 |
                |   2 |   FAST DUAL                        |                        |     1 |       |     2   (0)| 00:00:01 |
                |*  3 |   COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |     4 |     8 |    42  (31)| 00:00:01 |
                -------------------------------------------------------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                
                   3 - filter(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/OWNER'),0,0,209715
                              20,0),50,1,2) AS VARCHAR2(30) )='MDSYS' AND INSTR(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VA
                              LUE(KOKBF$),'/ROW/SCHEMA_URL'),0,0,20971520,0),50,1,2) AS VARCHAR2(80) ),'w3')>0)
                
                
                Statistics
                ----------------------------------------------------------
                          0  recursive calls
                          0  db block gets
                          3  consistent gets
                          0  physical reads
                          0  redo size
                        583  bytes sent via SQL*Net to client
                        419  bytes received via SQL*Net from client
                          2  SQL*Net roundtrips to/from client
                          0  sorts (memory)
                          0  sorts (disk)
                          1  rows processed
                
                SQL> 1
                  1* select * from MY_EXTERNAL_XFILE where owner='MDSYS'
                
                SQL> c/MDSYS/XDB/
                  1* select * from MY_EXTERNAL_XFILE where owner='XDB'
                
                SQL> select * from MY_EXTERNAL_XFILE where owner='XDB'
                  2  and instr(SCHEMA,'w3') > 0;
                
                OWNER                          SCHEMA                                                                           MIN
                ------------------------------ -------------------------------------------------------------------------------- ---
                XDB                            http://www.w3.org/2001/csx.xml.xsd                                               NO
                XDB                            http://www.w3.org/2001/csx.XInclude.xsd                                          NO
                XDB                            http://www.w3.org/2001/XInclude.xsd                                              NO
                XDB                            http://www.w3.org/1999/csx.xlink.xsd                                             NO
                XDB                            http://www.w3.org/1999/xlink.xsd                                                 NO
                XDB                            http://www.w3.org/2001/xml.xsd                                                   NO
                
                6 rows selected.
                
                
                Execution Plan
                ----------------------------------------------------------
                Plan hash value: 2888251253
                
                -------------------------------------------------------------------------------------------------------------
                | Id  | Operation                          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
                -------------------------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT                   |                        |     4 |     8 |    44  (30)| 00:00:01 |
                |   1 |  NESTED LOOPS                      |                        |     4 |     8 |    44  (30)| 00:00:01 |
                |   2 |   FAST DUAL                        |                        |     1 |       |     2   (0)| 00:00:01 |
                |*  3 |   COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |     4 |     8 |    42  (31)| 00:00:01 |
                -------------------------------------------------------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                
                   3 - filter(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/OWNER'),0,0,209715
                              20,0),50,1,2) AS VARCHAR2(30) )='XDB' AND INSTR(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALU
                              E(KOKBF$),'/ROW/SCHEMA_URL'),0,0,20971520,0),50,1,2) AS VARCHAR2(80) ),'w3')>0)
                
                
                Statistics
                ----------------------------------------------------------
                         19  recursive calls
                          0  db block gets
                        333  consistent gets
                          0  physical reads
                          0  redo size
                        815  bytes sent via SQL*Net to client
                        419  bytes received via SQL*Net from client
                          2  SQL*Net roundtrips to/from client
                          0  sorts (memory)
                          0  sorts (disk)
                          6  rows processed
                
                SQL> select * from MY_EXTERNAL_XFILE where owner='XDB';
                
                OWNER                          SCHEMA                                                                           MIN
                ------------------------------ -------------------------------------------------------------------------------- ---
                XDB                            http://xmlns.oracle.com/xdb/csx.xmltr.xsd                                        NO
                XDB                            http://www.w3.org/2001/csx.xml.xsd                                               NO
                XDB                            http://xmlns.oracle.com/xdb/XDBResource.xsd                                      NO
                XDB                            http://xmlns.oracle.com/xdb/XDBSchema.xsd                                        NO
                XDB                            http://xmlns.oracle.com/xs/principal.xsd                                         NO
                XDB                            http://xmlns.oracle.com/xs/aclids.xsd                                            NO
                XDB                            http://xmlns.oracle.com/xs/dataSecurity.xsd                                      NO
                XDB                            http://xmlns.oracle.com/xdb/xdbconfig.xsd                                        NO
                XDB                            http://xmlns.oracle.com/xs/securityclass.xsd                                     NO
                XDB                            http://xmlns.oracle.com/xs/roleset.xsd                                           NO
                XDB                            http://xmlns.oracle.com/xdb/stats.xsd                                            NO
                XDB                            http://www.w3.org/2001/csx.XInclude.xsd                                          NO
                XDB                            http://www.w3.org/2001/XInclude.xsd                                              NO
                XDB                            http://www.w3.org/1999/csx.xlink.xsd                                             NO
                XDB                            http://www.w3.org/1999/xlink.xsd                                                 NO
                XDB                            http://xmlns.oracle.com/xdb/XDBFolderListing.xsd                                 NO
                XDB                            http://xmlns.oracle.com/xdb/xmltr.xsd                                            NO
                XDB                            http://www.w3.org/2001/xml.xsd                                                   NO
                XDB                            http://xmlns.oracle.com/xdb/log/httplog.xsd                                      NO
                XDB                            http://xmlns.oracle.com/xdb/log/ftplog.xsd                                       NO
                XDB                            http://xmlns.oracle.com/xdb/log/xdblog.xsd                                       NO
                XDB                            http://xmlns.oracle.com/xdb/XDBStandard.xsd                                      NO
                XDB                            http://xmlns.oracle.com/xdb/XDBResConfig.xsd                                     NO
                XDB                            http://xmlns.oracle.com/xdb/acl.xsd                                              NO
                XDB                            http://xmlns.oracle.com/xdb/dav.xsd                                              NO
                
                25 rows selected.
                
                
                Execution Plan
                ----------------------------------------------------------
                Plan hash value: 2888251253
                
                -------------------------------------------------------------------------------------------------------------
                | Id  | Operation                          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
                -------------------------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT                   |                        |    82 |   164 |    44  (30)| 00:00:01 |
                |   1 |  NESTED LOOPS                      |                        |    82 |   164 |    44  (30)| 00:00:01 |
                |   2 |   FAST DUAL                        |                        |     1 |       |     2   (0)| 00:00:01 |
                |*  3 |   COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |    82 |   164 |    42  (31)| 00:00:01 |
                -------------------------------------------------------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                
                   3 - filter(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/OWNER'),0,0,209715
                              20,0),50,1,2) AS VARCHAR2(30) )='XDB')
                
                
                Statistics
                ----------------------------------------------------------
                         19  recursive calls
                          0  db block gets
                        333  consistent gets
                          0  physical reads
                          0  redo size
                       1830  bytes sent via SQL*Net to client
                        430  bytes received via SQL*Net from client
                          3  SQL*Net roundtrips to/from client
                          0  sorts (memory)
                          0  sorts (disk)
                         25  rows processed
                • 5. Re: Error with basic XMLTable
                  Marco Gralike
                  Its the "*one* expression" that creates "the issues"...
                  SQL> SELECT *
                    2        FROM XMLTABLE ('/ROWSET/ROW'
                    3                       PASSING xmltype(cursor(select schema_url from all_xml_schemas))
                    4                       ) xtab
                    5 ;
                  
                  COLUMN_VALUE
                  -------------------------------------------------------------------------------------------------------------------
                  <ROW><SCHEMA_URL>http://xmlns.oracle.com/xdb/acl.xsd</SCHEMA_URL></ROW>
                  <ROW><SCHEMA_URL>http://xmlns.oracle.com/xdb/dav.xsd</SCHEMA_URL></ROW>
                  <ROW><SCHEMA_URL>http://xmlns.oracle.com/xdb/XDBResConfig.xsd</SCHEMA_URL></ROW>
                  <ROW><SCHEMA_URL>http://xmlns.oracle.com/xdb/XDBStandard.xsd</SCHEMA_URL></ROW>
                  <ROW><SCHEMA_URL>http://xmlns.oracle.com/xdb/log/xdblog.xsd</SCHEMA_URL></ROW>
                  <ROW><SCHEMA_URL>http://xmlns.oracle.com/xdb/log/ftplog.xsd</SCHEMA_URL></ROW>
                  <ROW><SCHEMA_URL>http://xmlns.oracle.com/xdb/log/httplog.xsd</SCHEMA_URL></ROW>
                  <ROW><SCHEMA_URL>http://www.w3.org/2001/xml.xsd</SCHEMA_URL></ROW>
                  <ROW><SCHEMA_URL>http://xmlns.oracle.com/xdb/xmltr.xsd</SCHEMA_URL></ROW>
                  <ROW><SCHEMA_URL>http://xmlns.oracle.com/xdb/XDBFolderListing.xsd</SCHEMA_URL></ROW>
                  <ROW><SCHEMA_URL>http://www.w3.org/1999/xlink.xsd</SCHEMA_URL></ROW>
                  <ROW><SCHEMA_URL>http://www.w3.org/1999/csx.xlink.xsd</SCHEMA_URL></ROW>
                  <ROW><SCHEMA_URL>http://www.w3.org/2001/XInclude.xsd</SCHEMA_URL></ROW>
                  <ROW><SCHEMA_URL>http://www.w3.org/2001/csx.XInclude.xsd</SCHEMA_URL></ROW>
                  ...
                  
                  
                  SQL> SELECT *
                    2        FROM XMLTABLE ('/ROWSET/ROW'
                    3                       PASSING xmltype(cursor(select owner,schema_url from all_xml_schemas))
                    4                      ) xtab;
                  
                                       PASSING xmltype(cursor(select owner,schema_url from all_xml_schemas))
                                        *
                  
                  ERROR at line 3:
                  ORA-00600: internal error code, arguments: [qmxtrGetRealOPn], [], [], [], [], [], [], [], [], [], [], []