SQL to search objects, including stored procedures, in Oracle

Question

I need to write some sql that will allow me to query all objects in our Oracle database. Unfortunately the tools we are allowed to use don't have this built in. Basically, I need to search all tables, procedures, triggers, views, everything.

I know how to search for object names. But I need to search for the contents of the object. i.e. SELECT * FROM DBA_OBJECTS WHERE object_name = '%search string%';

Thanks, Glenn

Garb

A good resource: techonthenet.com/oracle/sys_tables – Garb

Answer 1

i'm not sure if i understand you, but to query the source code of your triggers, procedures, package and functions you can try with the "user_source" table.

select * from user_source

Illgotten

Answer 2

I'm not sure I quite understand the question but if you want to search objects on the database for a particular search string try:

SELECT owner, name, type, line, text 
FROM dba_source
WHERE instr(UPPER(text), UPPER(:srch_str)) > 0;

From there if you need any more info you can just look up the object / line number.

For views you can use:

SELECT *
FROM dba_views
WHERE instr(UPPER(text_vc), UPPER(:srch_str)) > 0

Scimitar

use SELECT owner, name, type FROM dba_source WHERE instr(UPPER(name), UPPER(':srch_strg')) > 0; for only searching in names .. maybe more practical for some – Fimbriate

This approach is great because you can filter the 'type' of objects. (WHERE type = 'PROCEDURE') if needed.. Saves you from going through all of the other types if you know that it is a procedure or maybe a function (for example...) – Ocreate

Answer 3

I would use DBA_SOURCE (if you have access to it) because if the object you require is not owned by the schema under which you are logged in you will not see it.

If you need to know the functions and Procs inside the packages try something like this:

select * from all_source
 where type = 'PACKAGE'
   and (upper(text) like '%FUNCTION%' or upper(text) like '%PROCEDURE%')
   and owner != 'SYS';

The last line prevents all the sys stuff (DBMS_ et al) from being returned. This will work in user_source if you just want your own schema stuff.

Overt

Answer 4

i reached this question while trying to find all procedures which use a certain table

Oracle SQL Developer offers this capability, as pointed out in this article : https://www.thatjeffsmith.com/archive/2012/09/search-and-browse-database-objects-with-oracle-sql-developer/

From the View menu, choose Find DB Object. Choose a DB connection. Enter the name of the table. At Object Types, keep only functions, procedures and packages. At Code section, check All source lines.

Budweis

Answer 5

ALL_SOURCE describes the text source of the stored objects accessible to the current user.

Here is one of the solution

select * from ALL_SOURCE where text like '%some string%';

Blowhole

Answer 6

In Oracle 11g, if you want to search any text in whole database or procedure below mentioned query can be used:

select * from user_source WHERE UPPER(text) LIKE '%YOUR SAGE%'

Aram