Oracle VM Consulting
view counter

Finding stuff in ALL_VIEWS using XML

Thanks to Scott Wesley for this story

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

view counter

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||''''))

Read the entire article at its source

view counter