Avatar of Jamil Muammar
Jamil Muammar
 asked on

Oracle SQL Search text in views code

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
Oracle DatabaseSQL

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Russ Suter

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
slightwv (䄆 Netminder)

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck