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.
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.
ReplyDeleteRegards,
Adrian
Interesting. Something I'll have to read through again when it's not almost bed time!
ReplyDeleteCheers