APEXblog.nl - Tips and Tricks

About This Blog:
I (Richard Weug) started this blog primary for myself to save all kinds of Apex tips and tricks in one place. To use it as a place to write down how I used some coding in my own projects, but also to copy and paste all kinds of articles I find on the Internet. (So I never have to wonder on what website did I read about??? When I see something interesting I collect the content so I have my own archive/knowlegde base.

View my profile on LinkedIn

 

PL/SQL stuff

Print
07
May

Find in all ALL_VIEWS

Written by Richard Weug. Posted in PL/SQL

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

Original article written by L  Scott Wesley

link:  http://www.grassroots-oracle.com/2012/04/finding-stuff-in-allviews-using-xml.html