SQL to search objects, including stored procedures, in Oracle

full-text-searchoraclesearchsql

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

Best Answer

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

Related Solutions

Sql – Function vs. Stored Procedure in SQL Server

Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e., no INSERT or UPDATE statements allowed).

A function can be used inline in SQL statements if it returns a scalar value or can be joined upon if it returns a result set.

A point worth noting from comments, which summarize the answer. Thanks to @Sean K Anderson:

Functions follow the computer-science definition in that they MUST return a value and cannot alter the data they receive as parameters (the arguments). Functions are not allowed to change anything, must have at least one parameter, and they must return a value. Stored procs do not have to have a parameter, can change database objects, and do not have to return a value.

Oracle: search though all stored procs/triggers/other db code

It is possible to search through object code-- you'd generally use the DBMS_METADATA package to generate the DDL for the object and then search the CLOB. However, it doesn't sound like that's actually what you want to do.

If you are just trying to figure out whether a table is referenced by any code in your system, you would generally want to use the DBA_DEPENDENCIES view (or ALL_DEPENDENCIES or USER_DEPENDENCIES depending on your privileges and the scope of what you're looking for). Something like

SELECT *
  FROM dba_dependencies
 WHERE referenced_owner = 'SCOTT'
   AND referenced_name  = 'EMP'
   AND referenced_type  = 'TABLE'

will show you everything that depends on the EMP table in the SCOTT schema.

The only time you'd want to search code rather than looking at DBA_DEPENDENCIES would be when you had code that was doing dynamic SQL where the table name was hard-coded. But that's relatively unlikely in practice.

Best Answer

Related Solutions

Sql – Function vs. Stored Procedure in SQL Server

Oracle: search though all stored procs/triggers/other db code

Related Topic