1 Reply Latest reply: Aug 26, 2011 9:42 AM by odie_63 RSS

    Count Distinct-Values ORA-01762: vopdrv: view query block not in FROM

    883286
      Greetings,
      I am experiencing consistent errors using SQL*Plus XQuery processor and the processor internal to the 11g database using XMLQUERY and XMLTABLE using count and distinct-values functions. In other cases where I have encountered bugs in these processors I have mostly been able to work around them. I encountered this problem using 11.2.0.1 and upgraded to 11.2.0.2 hoping for resolution. However, simple use of count and distinct-values fails.

      I am experiencing this error using a variety of documents both from XMLTYPE tables/columns and XDB repository. The example I provide below is from the following book:

      XQuery
      By: Priscilla Walmsley
      Publisher: O'Reilly Media, Inc.
      Pub. Date: March 30, 2007

      SQL> select * from v$version;
      Enter
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
      PL/SQL Release 11.2.0.2.0 - Production
      CORE     11.2.0.2.0     Production
      TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
      NLSRTL Version 11.2.0.2.0 - Production

      5 rows selected.

      SQL> set xquery baseuri "/home/learn/"
      SQL> xquery doc ("catalog.xml")
      2 /
      Enter
      Result Sequence
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      <catalog>
      <product dept="WMN">
      <number>557</number>
      <name language="en">Fleece Pullover</name>
      <colorChoices>navy black</colorChoices>
      </product>
      <product dept="ACC">
      <number>563</number>
      <name language="en">Floppy Sun Hat</name>
      </product>
      <product dept="ACC">
      <number>443</number>
      <name language="en">Deluxe Travel Bag</name>
      </product>
      <product dept="MEN">
      <number>784</number>
      <name language="en">Cotton Dress Shirt</name>
      <colorChoices>white gray</colorChoices>
      <desc>Our <i>favorite</i> shirt!</desc>
      </product>
      </catalog>


      1 item(s) selected.

      SQL> xquery
      2 let $depts := doc ("catalog.xml")/catalog/product/@dept
      3 return count ($depts)
      4 /
      Enter
      Result Sequence
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      4

      1 item(s) selected.

      SQL> edit
      Wrote file afiedt.buf

      1 xquery
      2 let $depts := doc ("catalog.xml")/catalog/product/@dept
      3* return count (distinct-values ($depts))
      SQL> /
      ERROR:
      ORA-01762: vopdrv: view query block not in FROM


      SQL> select column_value from xmltable ('declare base-uri "/home/learn/"; (: :)
      2 let $depts := doc ("catalog.xml")/catalog/product/@dept
      3 return count ($depts)
      4 ');
      Enter
      COLUMN_VALUE
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      4

      1 row selected.

      SQL> edit
      Wrote file afiedt.buf

      1 select column_value from xmltable ('declare base-uri "/home/learn/"; (: :)
      2 let $depts := doc ("catalog.xml")/catalog/product/@dept
      3 return count (distinct-values ($depts))
      4* ')
      SQL> /
      select column_value from xmltable ('declare base-uri "/home/learn/"; (: :)
      *
      ERROR at line 1:
      ORA-01762: vopdrv: view query block not in FROM


      Any assistence will be greatly appreciated.

      Best Regards
        • 1. Re: Count Distinct-Values ORA-01762: vopdrv: view query block not in FROM
          odie_63
          Hi,
          I am experiencing consistent errors using SQL*Plus XQuery processor and the processor internal to the 11g database using XMLQUERY and XMLTABLE using count and distinct-values functions.
          Actually SQL*Plus XQUERY command, XMLQuery and XMLTable use the same implementation of XQuery.

          You should contact Oracle Support for that error, as it strongly looks like an internal error.
          More specifically, it's probably related to XQuery optimization process because it works with the NO_XML_QUERY_REWRITE hint :
          SQL> declare
            2   res boolean;
            3  begin
            4   res := dbms_xdb.CreateFolder('/home/learn');
            5   res := dbms_xdb.CreateResource('/home/learn/catalog.xml', '<catalog>
            6  <product dept="WMN">
            7  <number>557</number>
            8  <name language="en">Fleece Pullover</name>
            9  <colorChoices>navy black</colorChoices>
           10  </product>
           11  <product dept="ACC">
           12  <number>563</number>
           13  <name language="en">Floppy Sun Hat</name>
           14  </product>
           15  <product dept="ACC">
           16  <number>443</number>
           17  <name language="en">Deluxe Travel Bag</name>
           18  </product>
           19  <product dept="MEN">
           20  <number>784</number>
           21  <name language="en">Cotton Dress Shirt</name>
           22  <colorChoices>white gray</colorChoices>
           23  <desc>Our <i>favorite</i> shirt!</desc>
           24  </product>
           25  </catalog>');
           26  end;
           27  /
           
          PL/SQL procedure successfully completed
           
          SQL> commit;
           
          Commit complete
           
          SQL> 
          SQL> select /*+ no_xml_query_rewrite */
            2         xmlcast(
            3           xmlquery(
            4           'declare base-uri "/home/learn/"; (::)
            5            let $depts := fn:doc("catalog.xml")/catalog/product/@dept
            6            return count(distinct-values($depts))'
            7           returning content
            8           )
            9         as number
           10         ) as result
           11  from dual
           12  ;
           
              RESULT
          ----------
                   3
           
          I don't have the error using an XMLType table :
          SQL> create table test_xml of xmltype
            2  xmltype store as securefile binary xml
            3  ;
           
          Table created
           
          SQL> 
          SQL> insert into test_xml values(xmltype('<catalog>
            2  <product dept="WMN">
            3  <number>557</number>
            4  <name language="en">Fleece Pullover</name>
            5  <colorChoices>navy black</colorChoices>
            6  </product>
            7  <product dept="ACC">
            8  <number>563</number>
            9  <name language="en">Floppy Sun Hat</name>
           10  </product>
           11  <product dept="ACC">
           12  <number>443</number>
           13  <name language="en">Deluxe Travel Bag</name>
           14  </product>
           15  <product dept="MEN">
           16  <number>784</number>
           17  <name language="en">Cotton Dress Shirt</name>
           18  <colorChoices>white gray</colorChoices>
           19  <desc>Our <i>favorite</i> shirt!</desc>
           20  </product>
           21  </catalog>'));
           
          1 row inserted
           
          SQL> 
          SQL> select xmlcast(
            2           xmlquery(
            3           'let $depts := /catalog/product/@dept
            4            return count(distinct-values($depts))'
            5           passing object_value
            6           returning content
            7           )
            8         as number
            9         ) as result
           10  from test_xml
           11  ;
           
              RESULT
          ----------
                   3
           
          SQL> 
          SQL> select xmlcast(
            2           xmlquery(
            3           'count(distinct-values(/catalog/product/@dept))'
            4           passing object_value
            5           returning content
            6           )
            7         as number
            8         ) as result
            9  from test_xml
           10  ;
           
              RESULT
          ----------
                   3
           
          Edited by: odie_63 on 26 août 2011 18:42