Hi,
I needed to search for some specific text in the DDL of each of the views in a particular schema. The text column of the user_views is LONG, and I looked at some old Tom's threads for converting long2clob, but found these processes to be really cumbersome, so I just opted to use DBMS_METADATA.GET_DDL instead even if it is a little slow.
One area I do not have a lot of experience with is searching clob fields. I tried this but not sure what would be the appropriate function to use for something like this:
SELECT *
FROM
(
SELECT
object_name,
object_type,
DBMS_METADATA.GET_DDL(
object_type => 'VIEW',
NAME => object_name,
SCHEMA => 'SCHEMA123'
) object_text
FROM all_objects
WHERE object_type = 'VIEW'
AND owner = 'SCHEMA123'
)
WHERE contains(object_text, 'WHERE t.policy NOT LIKE') > 0; -- Show the names of all views that contain the matching text