Request, enhancement of Find Database Objects

[Claus Pedersen]

When seaching for code in packages, triggers, functions, procedures, java, and other source that can be found in the data dictionary view all_source, the speed of the search can be enhanced significantly if you perform the search in the all_source view directly.

Especially when searching remote databases, connected for instance via VPN connections, today, the search can take up to 10 or 20 minutes using Find Database Objects (we typically have some hundred thousand lines of code in a shema).

If the user has chosen to search for one of the object types above, a query like

SELECT * FROM all_source WHERE text LIKE '%MyCode%'

can return the relevant objects and line numbers in a matter of seconds. I always use this method (if I remember it!) when connected to remote databases instead of using Find Database Objects.

If the user has unchecked "Case sensitive" the query could look like

SELECT * FROM all_source WHERE lower(text) LIKE '%' || lower('MyCode') || '%'

Also some of the "Object criteria" options can easily be implemented using the all_source approach.

The selection "Whole words only" can be executed in the same manner including the allowed white space characters: ,, semicolon, paranthesis, assignment, operators etc.

The selection "Regular expressions" could also be performed by SQL by using some of Oracle's own methods for regular expressions.

The two latter solutions can maybe be a little tricky to implement, but the daily search for variable names, table names etc. inside code without using any special search criteria, can be speeded up significantly by using all_source.

It could eventually be a checkbox option in the "Find Database Objects" dialogue: "Use all_source for search", then the user could choose his own preference.

 

[Marco Kalter]

I have added this to the list of enhancement requests.

 

[Claus Pedersen]

Thanks

 

[Claus Pedersen]

Will this be implemented in version 11?

It will speed up most normal searches in PL/SQL code (functions/procedures/packages/types/triggers) by at least a factor 10-100. Instead of fetching all code to the client and then perform the search on the client, it is far more efficient to make a select in the Oracle database and then return just the names of the resulting packages, triggers etc.

This has been on the request list for nearly 7 years now, so maybe it is time to let it float a little closer to the surface?