Thursday 10 September 2009

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.

2 comments:

Adrian Billington said...

I wrote something called the "Dictionary Long Application" that uses pipelined functions to workaround this problem by encapsulating the dictionary views with LONG columns. The DLA converts LONGs to CLOBs (which allows all the string searching). It's available here and the zip includes a fixed 9i/10gR1 version and a completely dynamic 10gR2+ version. I also wrote about the latter here.

Regards,
Adrian

Scott Wesley said...

Interesting. Something I'll have to read through again when it's not almost bed time!

Cheers