We help IT Professionals succeed at work.

Oracle SQL Search text in views code

101 Views
Last Modified: 2018-09-10
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

Senior Software Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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