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:

About these ads

One Response to “Search code in SQL Developer 3.0”


  1. Excelente aporte, espero que superen esta debilidad para las siguientes versiones de sql developer, muy agradecido!!!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: