Oracle SQL Search text in views code

Jamil Muammar
Jamil Muammar used Ask the Experts™
on
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"

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Software Developer
Commented:
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:
CREATE TABLE temp_Views AS
SELECT OWNER, VIEW_NAME, TEXT_LENGTH, to_lob(TEXT) TEXT FROM all_views;

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

Commented:
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


or

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
xmltable('/ROWSET/ROW'
	passing dbms_xmlgen.getxmltype('select view_name, text from user_views')
	columns
		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