Finding stuff in ALL_VIEWS using XML

Quite some time ago when I first started blogging I wrote a post regarding limitations on searching the ALL_VIEWS dictionary view.

Simply put, if you would like to run a query such as this to find those views that contain the text "booking_no", you're not going to get very far.

SQL> select count(*)
2  from user_views
3  where text like '%booking_no%';
where text like '%booking_no%'
*
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

I can't remember where I found this, but there is a way to do perform this search without pumping your data into a temporary table.

select count(*)
from user_views 
where upper(dbms_xmlgen.getxml('select text from user_views where view_name = '''||view_name||'''')) 
  like upper('%booking_no%')

It's essentially looking for "booking_no" within the result of a query that has been converted into canonical XML format.

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <TEXT>SELECT r.code,b.booking_no,e.start_date,e.end_date,o.org_id,o.name
FROM   resources r,bookings b,events e,organisations o
WHERE  r.code = b.resource_code
AND    b.event_no = e.event_no
AND    o.org_id = e.org_id</TEXT>
 </ROW>
</ROWSET>

A word of warning, it is CPU (and probably memory) intensive, so you might like to limit to a particular set of schemas instead of just searching ALL_VIEWS.

Turns out XML has some nifty uses after all ;-)