This discussion is archived
5 Replies Latest reply: Jul 29, 2010 10:38 AM by MarcoGralike RSS

Error with basic XMLTable

783942 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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], [], [], [], [], [], [], [], [], [], [], []

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points