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"

Jamil MuammarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russ SuterSenior Software DeveloperCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) 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


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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.