Search code in SQL Developer 3.0
June 22, 2011
Oracle 11g introduced “syntax aware” code search facility — PL/Scope. One can look up identifier definitions, usages, exceptions, which is much more sophisticated and targeted search compared to the old way of finding all of the occurrences of a string in the ALL_SOURCE. Certainly few developers are expected to use PL/Scope À la carte. IDEs, such as SQL Developer, accommodated it within its [object] search. Here is a screen shot illustrating a search of formal parameter P3:
So, with PL/Scope available is the old way, that is finding all of the occurrences of a string in the ALL_SOURCE, obsolete? Yes, it has been effectively deprecated, which turned out to be ill-considered decision. Apparently, some people like the old way better, especially that there is no way to search the comments. The fix is expected in next SQL Developer release, but for now here is a workaround. Among the other things it demonstrates that many features are nearly duplicated, and certainly a search can be accommodated by SQL Developer reports. In fact, there is a off-the-shelf report “Search Source Code” already, although many would find the owner = USER predicate impeding their goal. With little effort, however, one can customize it to approximate to what the Search Panel does (for RDBMS versions below 11.1). One can even prettify the output with html formatting; here are the steps:
1. Go to reports tab -> User Defined Reports and select “Add Report” from context menu
2. Name the report, e.g. “Fancy all_source”, and copy and paste the following prettified query
select
owner "Owner",
name "PL/SQL Object Name",
type "Type",
line "Line",
'<html><font bgcolor=#dddddd>'
||substr(text,1,instr(upper(text),upper(:TEXT_STRING))-1)||'</font>'
||'<font bgcolor=#bbffbb>'
||substr(text,instr(upper(text),upper(:TEXT_STRING)),length(:TEXT_STRING))
||'</font>'
||'<html><font bgcolor=#dddddd>'
||substr(text,instr(upper(text),upper(:TEXT_STRING))+length(:TEXT_STRING))
"Text",
owner sdev_link_owner,
name sdev_link_name,
type sdev_link_type,
line sdev_link_line
from sys.all_source
where (:TEXT_STRING is null or
instr(upper(text),upper(:TEXT_STRING)) > 0)
and length(text)<3000 --not like '%wrapped%'
and name not like 'BIN$%'
order by owner, name, type, line
3. Use Go To option from context menu on PL/SQL object name to navigate to the PL/SQL compilation unit.
Here is a screen shot of the report output:
May 25, 2012 at 6:55 pm
Excelente aporte, espero que superen esta debilidad para las siguientes versiones de sql developer, muy agradecido!!!