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
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 SuterCommented:
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.
0

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


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

0
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
SQL

From novice to tech pro — start learning today.