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

Executing CLOBs as DDL

How to kill your session

11g> declare
  2  v_clob clob :=  EMPTY_CLOB(); -- initialize clob
  3  begin
  4  execute immediate v_clob;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8540
Session ID: 196 Serial number: 364

How to execute it properly

11g> declare
  2  v_clob clob :=  EMPTY_CLOB();
  3  begin
  4  v_clob := to_clob('begin null; end;'); -- assign ddl value to clob
  5  execute immediate v_clob;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Something you couldn't do in 10g

10g> declare
  2  v_clob clob :=  EMPTY_CLOB();
  3  begin
  4  v_clob := to_clob('begin null; end;');
  5  execute immediate v_clob;
  6  end;
  7  /
execute immediate v_clob;
                  *
ERROR at line 5:
ORA-06550: line 5, column 19:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

Related OTN post.

Documentation here.

Related to an idea regarding lifecycle management.

A LONG view ahead

Every time a new version is release I feel compelled to have a little peak at the definition of ALL_VIEWS, to see if columns such as ALL_VIEWS.TEXT have been converted from the LONG datatype. Alas, 11gR2 still has this utilises the LONG datatype, perhaps this is just something that has to be persistent through the ages.

However we can still get around the issue. The reason I find this of interest is sometimes during an impact analysis of say - dropping a column from a table, we need to determine what objects refer to this column. We could start with ALL_DEPENDENCIES, but this isn't granular enough for some analyses.

Due to the many restrictions with the LONG datatype, we can't use the INSTR function to search this dictionary view. Here's one work-around:

> DROP TABLE my_views;

> CREATE TABLE my_views(text CLOB);

> INSERT INTO my_views (text)
SELECT TO_LOB(text) 
FROM all_views;

SAGE@sw10g> SELECT *
  2  FROM   my_views
  3  WHERE INSTR(lower(text),'hiredate') > 0;

TEXT
------------------------------------------------------------------------
select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"

1 row selected.

Got any others?

I do! 
Here is my posting from April 2012 on using XML to solve the problem.