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.
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.
nothing is wrong with that query...
make sure you have some source that has BLAH...
make sure you have some source that has BLAH...
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.
Thanks for prompt response.
I get these errors.
ORA-31603: object "#T0_sid:138762780_4_Group
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.
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
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
ASKER
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
I'm sure BLAH is contained in the SQL
ASKER
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.
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;
/
ASKER
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?
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.
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.
ASKER
LVL77, I ran the loop you sent and get this output 'anonymous block completed' - but no other output.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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#;
>>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.
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.
ASKER
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
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?
ASKER
slightwv,
Your loop is working now. I was using %BLAH% as search string; it started working when I just used BLAH.
Thanks
Your loop is working now. I was using %BLAH% as search string; it started working when I just used BLAH.
Thanks
ASKER
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%)
This solution worked after I activated the DBMS Output view window, and only entered search text (e.g. BLAH) not (%BLAH%)
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:
Open in new window