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>
Turns out XML has some nifty uses after all ;-)