Find a string within the source code (DDL) of a stored procedure in oracle

Question

I need to find a string in the source code (DDL) for all stored procedures in Oracle schema.

I use this query to perform the task, but I think can be improved

SELECT T0.OBJECT_NAME
FROM USER_PROCEDURES T0 
WHERE T0.OBJECT_TYPE='PROCEDURE'
AND INSTR( (SELECT DBMS_METADATA.GET_DDL('PROCEDURE',T0.OBJECT_NAME,'MySCHEMA') 
            FROM DUAL), 'TheStringToSearch' )>0

is there a way of accomplishing this task in a more optimal and fast way?

thanks in advance.

Answer 1

Yes, use USER_SOURCE:

select distinct name
from   user_source
where  type = 'PROCEDURE'
and    lower(text) like lower('%the_text_you_want%');