selecting text within all_views

I am trying to select some text within all_views but get an error.
I know the text column is a long datatype and when I query the view,
the text field shows the code for the views being selected.

--I want to find what views have a column name of 'project' being used within the view:
select * from all_views where owner = 'AJT' and text like '%project%';

Thanks

using Oracle 11g
MachinegunnerAsked:
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.

johnsoneSenior Oracle DBACommented:
Are you looking for a view that contains a column with project in the name or are you looking for a view that select a column with project in the name.

If looking for a column in a view with project in the name, then this would work:
select table_name from all_tab_columns where owner = 'AJT' and column_name like '%PROJECT%';
0
MachinegunnerAuthor Commented:
I am looking for the name within the text column or search within the text field that
has a value like project, not a specific column name within all_tab_columns, thanks for the reply
0
johnsoneSenior Oracle DBACommented:
Then you cannot search a LONG column that way.  I assume you are getting an inconsistent data type problem.

One way to get around that is to use the method described here -> https://community.oracle.com/thread/1091702  Basically you create a temporary table that contains the view text in a CLOB column.  Then you can search it with a LIKE.

You could probably easily write something with DBMS_METADATA to extract the text to CLOB in a PL/SQL loop and search it there.

Be aware of case.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MachinegunnerAuthor Commented:
yes, I was thinking of inserting the view data into a temp table and then doing the search from there.

you are correct, i was getting a "ORA-00932: inconsistent datatypes: expected NUMBER got LONG"
error when trying that select statement.

Too bad Oracle doesn't make it easy for you to just query on the text of a view.
0
slightwv (䄆 Netminder) Commented:
This "Should work" but there appears to be a bug where I get a ORA-31603 when I run it.

select view_name from (
select view_name, lower(dbms_metadata.get_ddl('VIEW',view_name,'AJT')) vw_ddl from all_views
)
where vw_ddl like '%project%'
/

Open in new window


Here is my work-around:
declare
	junk clob;
begin
	for i in ( select view_name, dbms_metadata.get_ddl('VIEW',view_name,'AJT') vw_ddl from user_views) loop
		junk := lower(i.vw_ddl);
		if instr(junk,'project') > 0 then
			dbms_output.put_line('Found in: ' || i.view_name);
		end if;
	end loop;
end;
/

Open in new window


>>Too bad Oracle doesn't make it easy for you to just query on the text of a view.

LONGs are a horrible data type!!!  Oracle should port all their objects to CLOBs and stop using LONGs but I'm not holding my breath!!!!!!!!
0
johnsoneSenior Oracle DBACommented:
LONGs were deprecated a long time ago.  They really shouldn't be in the dictionary anymore.  There used to be functions and packages that dealt with longs, but they don't exist anymore.  The DBMS_LOB functions were supposed to work with LONGs for a while, but it appears that doesn't work anymore either.

At one point, I thought the view text was in DBA_SOURCE.  But maybe that was triggers.  They moved the trigger source to DBA_SOURCE, why can't they move the view source there too?  Seems like an easy fix.

Trying to find a more elegant way of doing it, but none of them seem to work.  If I find one I'll post it.
0
MachinegunnerAuthor Commented:
Thanks for all the replies.
Sorry, but here is another stick to throw against the wheel.
I tried the code example but added a call to a remote server and got this error below...

Error starting at line : 46 in command -
declare
      junk clob;
begin
      for i in ( select view_name, dbms_metadata.get_ddl@prod_1('VIEW',view_name,'AJT') vw_ddl from user_views) loop
            junk := lower(i.vw_ddl);
            if instr(junk,'project') > 0 then
                  dbms_output.put_line('Found in: ' || i.view_name);
            end if;
      end loop;
end;
Error report -
ORA-06553: PLS-564: lob arguments are not permitted in calls to remote server
ORA-06512: at line 4
06553. 00000 -  "PLS-%s: %s"
*Cause:    
*Action:
0
johnsoneSenior Oracle DBACommented:
I will say that I found a better way to create the temporary table that holds the view definitions.

create table my_view_text (owner varchar2(128), view_name varchar2(128), text clob);
insert into my_view_text (select owner, view_name, to_lob(text) from all_views);

Then you can easily query that temporary table with a LIKE operator.
0
slightwv (䄆 Netminder) Commented:
>>ORA-06553: PLS-564: lob arguments are not permitted in calls to remote server

Yep.  That is an issue.  LOB operations across links are limited.
0
MachinegunnerAuthor Commented:
10-4 on the lob operations across a remote server.
I tried johnson's code on creating a temp table and inserting the all_views
data and that works fine locally but when I try and access the remote server,
it gives me an error as well.

Thanks for the replies
0
johnsoneSenior Oracle DBACommented:
I was trying to do a substr off of the dbms_metadata (i.e.  DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL(...)))and for some reason it isn't working.  Not getting an error, just not getting the data.  That would force character data, but you could only get the first 4000 characters.  If you have big views it wouldn't work anyway.
0
slightwv (䄆 Netminder) Commented:
Any reason you cannot connect to the remote database directly and issue the dbms_metadata method?
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
MachinegunnerAuthor Commented:
I contacted the DBA for access to that remote server and will get it and try the dbms_metadata.

Thanks again!
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
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.