ORA-00904 Error using master-detail browser with view

Toad for Oracle
1

Hi All,

I have a view that queries against an XMLtype column that stores the XML in binary format.

When I point TOAD’s master-detail browser at the view I get the following error:

ORA-00904: “M”.“ROWID”: invalid identifier

I am using TOAD 12.1 with Oracle 11g R2.

Can anybody shed any light please?

Thanks in advance.

Martyn

2

Toad has problems selecting from binary XML. Cast it to a CLOB like this and you’ll be OK.

SELECT yourxmltypecolumn.GetClobVal() as YourXMLTypeColumn,

other_columns

from table

you can use the view/edit query button in the master/detail browser to change the query.

3

Hi John, thanks for the rapid reply.

My view is set up like this:

create view … as

SELECT column_a, column_b

FROM table_with_xml_col xtab,

XMLTABLE ( ‘xquery’ PASSING xtab.xml_column

COLUMNS column_a TYPE PATH ‘xpath expression a’,

column_b TYPE PATH ‘xpath expression b’) as ALIAS;

So, I do not select the whole XMLtype, rather the view is intended to pick out various elements from the XML and present them liek a traditional relational table.

I can’t see how to cast as you suggest in this scenario - sorry.

Regards,

Martyn

4

Oh, I think it just doesn’t like the fact that the MD Browser is selecting ROWID. It’s supposed to try again without ROWID when that happens, but I see that it’s not doing that. I’ll have that fixed in the next beta.

5

For now, as a workaround, set the TYPE dropdown to “Query” and just enter the query instead of selecting the view from the Owner and Name dropdowns.