CODEWITHSUNDEEP
sqloracle11g
AndyDan
AndyDan

Reputation: 759

Query by a LONG column

We have a big project install coming up, and I'm writing SQL scripts to do some of the installation verification. Some of the views that are being modified are only different in the query, not in the names or numbers of columns.

These are hard to verify because dba_views stores the query text in a LONG column. Ordinarily, I would use a query like this:

select *
  from dba_views
 where name = 'LOCKED_DOCUMENTS_V'
   and instr(upper(text), 'TLP') > 0;

to see if the view contained a particular text string it's code, but that doesn't work on a long column.

The only thing I have come up with so far is that I can check the text_length from dba_views, but it would be better to check the actual query.

Any thoughts on how to do this in a SQL script, which will be executed in SQL*Plus?

Thanks, Dan

Upvotes: 1

Views: 1356

Answers (1)

John D
John D

Reputation: 2365

Hey I'd use DBMS_METADATA.GET_DDL like the following:

select 
   dbms_lob.instr(clob_contents,'TLP')
from 
   (select 
        dbms_metadata.get_ddl('VIEW','LOCKED_DOCUMENTS_V', 'SCHEMA') clob_contents 
    from 
        dual) get_clob

Which will get you the position - as long as it is > 0 you are happy it is there.

Upvotes: 1

Related Questions