How to Search a String in Oracle Database (all_source)

Yes by telling you to search a string in Oracle database, I mean is search a string in all stored procedures, functions and packages of Oracle Database.  Because it happens sometimes that you want to find all database objects containing a particular string.

To do this you can use ALL_SOURCE dictionary object. Because it contains all source information line by line. Below is the SQL statement to query all_source view to search a particular string ('Employee Name:').

SELECT *
 FROM all_source
 WHERE LOWER (text) LIKE LOWER ('%Employee Name:%');

You can see the above result that it is having the information about the Function named F_BULKCOLLECT containing that string at line 15. You can easily find all source code for a string with the above query.  Because TEXT column of ALL_SOURCE view contains code text. Also notice that in query I am using lower() function both side for comparison.

You can further modify the query to obtain specific results. For example you can include criteria for OWNER to get the result for particular User / Schema. Also you can include criteria for TYPE to get the result for particular object types.

To get the result for particular schema / user:

SELECT *
 FROM all_source
 WHERE LOWER (text) LIKE LOWER ('%Employee Name:%')
 and owner = 'VINFORM';

To get the result for particular object type function / procedure or package:

SELECT *
 FROM all_source
 WHERE LOWER (text) LIKE LOWER ('%Employee Name:%')
 and type = 'FUNCTION';