Oracle SQL Search text in views code

Jamil Muammar
Jamil Muammar used Ask the Experts™
Dear Experts,

How do I in Oracle SQL search a string in my schema views code ?

for example I need to know all the views in my schema which contain the string: "left join"

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Software Developer
View data is stored in a table called "ALL_VIEWS". Unfortunately, the view text itself is stored in a LONG data field which cannot be searched. To accomplish what you need you'll have to insert the data into a new table created from ALL_VIEWS where the LONG field is converted to a CLOB. Then you can perform a regular text search. It would look something like this:

SELECT * FROM temp_Views WHERE TEXT LIKE '%left join%';

DROP TABLE temp_Views;

Open in new window

A couple of notes.
1. Depending on what program you're using to access the data, you may need to worry about case sensitivity. For those issues you can always alter your SELECT statement's WHERE clause "WHERE LOWER(TEXT) LIKE ..."
2. This won't be fast. In fact, it'll be miserably slow. Fortunately you'll probably have a small enough number of views that it won't matter.
3. Don't forget to drop the table again when you're done.
4. ALL_VIEWS will only show you views to which you have been granted permission. Make sure you run this query as a sysadmin or other privileged user to get a complete result set.
Most Valuable Expert 2012
Distinguished Expert 2018

Even though you already accepted an answer to this, you don't have to create a temp table for this.

It also won't find a view like:
create or replace view junk_vw as
select d1.dummy from dual d1 left
join  dual d2 on d1.dummy=d2.dummy

Open in new window


create or replace view junk_vw as
select d1.dummy from dual d1 LEFT JOIN  dual d2 on d1.dummy=d2.dummy

Open in new window

Try this that doesn't require the temp table:
select view_name from
	passing dbms_xmlgen.getxmltype('select view_name, text from user_views')
		view_name varchar2(128) path 'VIEW_NAME',
		view_text clob path 'TEXT'
where lower(replace(view_text,chr(10),' ')) like '%left join%'

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial