Buscar texto en procedimientos, funciones, objetos de oracle

Hubo momentos en que necesitaba buscar si una función se encontraba dentro de algún procedimiento, paquete o si se usaba dentro de otra función. Usando toad para oracle, se puede realizar búsquedas en todos los objetos con la opción «object search», pero si da problemas o no trabajas con toad, también se puede realizar las búsquedas con comandos sql.

Para buscar en objetos (paquetes, funciones, procedimientos, etc) podemos buscar en dba_source o all_source.

dba_source: todos los objetos de la base de datos en donde ejecutemos el sql.
all_source: todos los objetos accesibles por el usuario conectado actual.

Ejemplo:

SELECT name, line, type, text
FROM dba_source
WHERE upper(text) like upper('%TEXTO_BUSQUEDA%') escape '\'

SELECT name, line, type, text

FROM dba_source

WHERE upper(text) like upper('%TEXTO_BUSQUEDA%') escape '\'

Esto nos indica el nombre del objeto, línea donde se encontró el criterio, el tipo de objeto (función, paquete, procedimiento, etc.) y en el text se encuentra todo el cuerpo que define el objeto.

En vistas

Si necesitas buscar en vistas, puedes usar esta otra opción.

all_views: Todas las vistas incluidas las internas del sistema de oracle.
user_views: Vistas que pertenezcan al usuario actual

Para oracle mayor o igual a la versión >= 12 es muy simple con el siguiente sql:

SELECT *
FROM all_views
WHERE lower(text) LIKE '%abc%';

SELECT *

FROM all_views

WHERE lower(text) LIKE '%abc%';

Ahora para versiones anteriores a la versión 12, se necesita varios pasos adicionales porque el campo text en las vistas son establecidas como long y no podemos ejecutar una búsqueda de texto, ya que al convertir a string y superar el límite de 4000 caracteres, nos generará un error. Para evitar esto se genera una tabla temporal conteniendo esa columna en texto y ya podremos realizar la búsqueda por dicho criterio:

-- Creamos la tabla temporal como copia de las vistas:
CREATE GLOBAL TEMPORARY TABLE vistas_tmp AS SELECT view_name nombre_vista, to_lob(text) texto_vista FROM user_views;

-- Ingresamos los datos de la vista en la tabla temporal:
INSERT INTO vistas_tmp SELECT view_name nombre_vista, to_lob(text) texto_vista FROM user_views;

-- Realizamos la consulta de la tabla temporal:
SELECT * FROM vistas_tmp WHERE upper(texto_vista) LIKE upper('%TEXTO_BUSQUEDA%')  ESCAPE '\';

-- Eliminamos la tabla temporal
DROP TABLE vistas_tmp;