This discussion is archived
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 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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

Legend

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