How to write an XQUERY call with ora:view against another schema

Question

I want to write an XQUERY call in Oracle SQL Developer but I keep getting an error.

I have created a schema and table:

ALTER SYSTEM SET deferred_segment_creation=false;
CREATE USER bookdb IDENTIFIED BY abc123;
GRANT RESOURCE TO bookdb;
GRANT UNLIMITED TABLESPACE TO bookdb;
ALTER SESSION SET CURRENT_SCHEMA=bookdb;
CREATE SEQUENCE book_id_seq START WITH 1 INCREMENT BY 1; 
CREATE TABLE book (
  id INTEGER NOT NULL PRIMARY KEY,
  title VARCHAR2(50) NOT NULL UNIQUE,
  originallanguage VARCHAR2(20) NOT NULL,
  genre VARCHAR2(20)
 );

Here is an example of a row in the table:

INSERT INTO book VALUES (book_id_seq.nextval,'Misty Nights','English','Thriller');

The table is found under "Other Users" in the SQL Developer object navigator.

If I just write SELECT * FROM BOOKDB.BOOK it works. But using the XQUERY command and ora:view function is not working for me.

The XQUERY command I want run is:

XQUERY
element Result {
for $lang in distinct-values(ora:view("BOOKDB.BOOK")//ORIGINALLANGUAGE/text())
let $sum := for $book in ora:view("BOOKDB.BOOK")//ORIGINALLANGUAGE
            return $book[text() = $lang]
return element Language {attribute name {$lang}, attribute Total {count($sum)}}}

But I get the following error:

Error at Command Line : 1 Column : 9
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

Is there something wrong with my query? Or is it the ora:view function?

What is the full query you are currently running? It would also be helpful to show your table/view structure as text (not an image) with data types, and to include sample data and expected results. — Alex Poole, May 09 '19 at 17:08
it just this. I don't want to use SQL/XML that is easily done with SELECT XMLQUERY but i want just XQuery function — Red Eyez, May 09 '19 at 17:14

Alex Poole · Accepted Answer · 2019-05-27T16:56:51.913

You are passing a single argument to ora:view, which should be the table name. At the moment you're trying to tell it to look for a table called BOOK in schema BOOKDB by using the SQL dot notation (BOOKDB.BOOK), but you're working in an XML context when you supply that value. As shown in the documentation you can specify the schema using the optional first argument to that function:

XQUERY
element Result {
for $lang in distinct-values(ora:view("BOOKDB", "BOOK")//ORIGINALLANGUAGE/text())
let $sum := for $book in ora:view("BOOKDB", "BOOK")//ORIGINALLANGUAGE
            return $book[text() = $lang]
return element Language {attribute name {$lang}, attribute Total {count($sum)}}}

Result Sequence                                                                 
--------------------------------------------------------------------------------
<Result><Language name="English" Total="1"></Language></Result>

(This is based on the unquoted column names in the DDL you added to the question, e.g. originallanguage; not the mixed-case value in the image you originally posted which implied you had quoted identifieres, e.g. "OriginalLanguage". And as discussed in comments/chat, here BOOKDB is a schema, not a database; the mix of terminology is confusing, as is have 'DB' as part of a schema name.)

This is just a client wrapper for an XMLTable call, incidentally:

select column_value from xmltable('element Result {
for $lang in distinct-values(ora:view("BOOKDB", "BOOK")//ORIGINALLANGUAGE/text())
let $sum := for $book in ora:view("BOOKDB", "BOOK")//ORIGINALLANGUAGE
            return $book[text() = $lang]
return element Language {attribute name {$lang}, attribute Total {count($sum)}}}')

which you could also do as an XMLQuery:

select xmlquery('element Result {
for $lang in distinct-values(ora:view("BOOKDB", "BOOK")//ORIGINALLANGUAGE/text())
let $sum := for $book in ora:view("BOOKDB", "BOOK")//ORIGINALLANGUAGE
            return $book[text() = $lang]
return element Language {attribute name {$lang}, attribute Total {count($sum)}}}'
  returning content)
from dual

Also, ora:view is deprecated; but you can do the same thing with fn:collection:

XQUERY
element Result {
for $lang in distinct-values(fn:collection("oradb:/BOOKDB/BOOK")//ORIGINALLANGUAGE/text())
let $sum := for $book in fn:collection("oradb:/BOOKDB/BOOK")//ORIGINALLANGUAGE
            return $book[text() = $lang]
return element Language {attribute name {$lang}, attribute Total {count($sum)}}}

Result Sequence                                                                 
--------------------------------------------------------------------------------
<Result><Language name="English" Total="1"></Language></Result>

ohh, Thank you, I thought Oracle supported XQuery functions for all tables regardless of types. thanks again :) — Red Eyez, May 09 '19 at 17:33
Yes, via `ora:view` (or `fn:collection`), if you're querying relational data. That wasn't entirely obvious form your question. I've shown that working too; but I'm not sure why you'd do this, rather than use basic SQL aggregation. — Alex Poole, May 09 '19 at 18:05
I don't mean to be rude, may I ask you something related to this question? — Red Eyez, May 26 '19 at 18:46
I am having a problem with the answer as it is giving me an error: SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist". I think the problem is with ora:view and I tried to change the syntax many times but no luck, My database name is BOOKDB and my table is BOOK, how can I Use this ora:view function to query my table? — Red Eyez, May 27 '19 at 14:49
@RedEyez - by 'database' do you really mean 'schema'? Please add the actual DDL (as text) for the table to your question, not just an image. Are you connected to the schema that owns the table when you run the query? — Alex Poole, May 27 '19 at 14:51
So please add those statements to the question.Does changing the code I showed to have "BOOK" instead of "BOOKDB" not work? — Alex Poole, May 27 '19 at 14:55

How to write an XQUERY call with ora:view against another schema

1 Answers1