How to query on LONG datatype columns

You can't search LONG data types columns directly, due to its restrictions, Oracle recommends that you convert existing LONG columns to LOB columns.

In this example, I need to search all the mviews where query like '%foo%', since the column QUERY is a LONG, the first step is to re-create the Dictionary Data Table ALL_MVIEWS and convert using to_lob() function :

CREATE TABLE all_mviews_clob AS SELECT mview_name, to_lob(query) AS query 
FROM all_mviews ;

--now I am able to search on the QUERY column
SELECT * FROM all_mviews_clob WHERE upper(query) LIKE '%FOO%' ;