Link to home
Start Free TrialLog in
Avatar of Richard Detsch
Richard Detsch

asked on

How do I search for a text string in my SQL code

I've written SQL code for numerous views in an Oracle database. How do I find all views with SQL code containing specific text.

I've tried
     SELECT *
    FROM ALL_SOURCE
   WHERE UPPER(text) LIKE UPPER('%BLAH%')

But this SQL code does not search the text within my views.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>But this SQL code does not search the text within my views.

user_views has a TEXT column that has it.  It is a LONG data type so you cannot just do a search of it.

See if this works:
select view_name from (
select view_name, dbms_metadata.get_ddl('VIEW',view_name) ddl from user_views
)
where upper(ddl) like '%BLAH%'
/

Open in new window

nothing is wrong with that query...

make sure you have some source that has BLAH...
Avatar of Richard Detsch

ASKER

LVL77
Thanks for prompt response.
I get these errors.

ORA-31603: object "#T0_sid:138762780_4_Group" of type VIEW not found in schema "GHGFEMP"
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344
ORA-06512: at line 1
31603. 00000 -  "object \"%s\" of type %s not found in schema \"%s\""
*Cause:    The specified object was not found in the database.
*Action:   Correct the object specification and try the call again.
>>make sure you have some source that has BLAH...

The question is about VIEWS not stored procedures or functions.  View source isn't in the ALL_SOURCE view.
you should use DBA_VIEWS

but it has LONG column,

you can use this function (in the link below) to search that view...

http://oracle.erkansaka.org/2009/04/searching-dbaviews-giving-text.html
If BLAH was in the SQL of a view, how would it display the output? Would it specifically state the name of the view?
I'm sure BLAH is contained in the SQL
PS BLAH is contained in both the SQL of some views, and the name of some other views.
>>ORA-31603: object "#T0_sid:138762780_4_Group" of type VIEW not found in schema "GHGFEMP"

There appears to be an interesting "feature" doing it all in a single select.  I'm seeing a few obscure bugs on Oracle Support but nothing 100% definite on a solution.

I scanned the code posted in the link by Huseyin.  It looks like it should work but it also seems like a lot of unnecessary code.  I didn't test it.


Here is a pl/sql loop that seems to get around the ORA-31603 feature.  

begin
	for i in (select view_name, dbms_metadata.get_ddl('VIEW',view_name) ddl from user_views) loop
		if instr(upper(i.ddl),'BLAH') > 0 then
			dbms_output.put_line(i.view_name);
		end if;
	end loop;
end;
/

Open in new window

My queries don't seem to recognize 'dba_views' . if I only enter select * from dba_views I get an error 'table or view does not exist'.

Is there a chance I need different terminology to have it search all views?
dba_views is a DBA level view and includes everything in the entire database.  You need privileges to use it.

ALL level views is everything you own plus everything you have been granted access to.  So, ALL_VIEWS shouldn't error but it may also include things you don't want.  USER level views is everything your current user owns.
LVL77, I ran the loop you sent and get this output 'anonymous block completed' - but no other output.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
source for dba_views is this:

  SELECT u.name,
         o.name,
         v.textlength,
         v.text,
         t.typetextlength,
         t.typetext,
         t.oidtextlength,
         t.oidtext,
         t.typeowner,
         t.typename,
         DECODE (BITAND (v.property, 134217728),
                 134217728, (SELECT sv.name
                               FROM superobj$ h, "_CURRENT_EDITION_OBJ" sv
                              WHERE h.subobj# = o.obj# AND h.superobj# = sv.obj#),
                 NULL),
         DECODE (BITAND (v.property, 32), 32, 'Y', 'N'),
         DECODE (BITAND (v.property, 16384), 16384, 'Y', 'N')
    FROM sys."_CURRENT_EDITION_OBJ" o, sys.view$ v, sys.user$ u, sys.typed_view$ t
   WHERE o.obj# = v.obj# AND o.obj# = t.obj#(+) AND o.owner# = u.user#;

Open in new window

>>source for dba_views is this:

If they don't have permission to query dba_views directly, how will the underlying code be useful?

You also likely need to be logged in as SYS to run the SQL directly.
slightwv
I can't easily use sqlplus.

I believe I have serveroutput turned on

I don't see starting just 'ScriptRuner Task' and then 'anonymous block completed' after about 3-seconds
Again:  What tool are you using to run this?
slightwv,
Your loop is working now. I was using %BLAH% as search string; it started working when I just used BLAH.

Thanks
Thanks for all those who gave me input.
This solution worked after I activated the DBMS Output view window, and only entered search text (e.g. BLAH) not (%BLAH%)