Link to home
Create AccountLog in
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
ASKER CERTIFIED SOLUTION
Avatar of Russ Suter
Russ Suter

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of slightwv (䄆 Netminder)
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