How to search a string/key value from PL/SQL code

From dba_source/all_source/user_source view you can find the text source of objects such as FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE and TYPE BODY. You can query from these views in order to find any string/key value inside the objects.

By following query you can find any tables or columns or any strings or expressions referenced in your PL/SQL code.

SELECT type, name, line
FROM user_source
WHERE UPPER(text) LIKE UPPER('%&KEYWORD%');

If you run above query from Sql*Plus then enter the string you are searching for when prompted for KEYWORD. If you use another tool to run above Sql then replace &KEYWORD with the string you are searching for.

Example: I search for Testing string and I found testing word is found inside P_TEST procedure at line number 4.

SQL> SELECT type, name, line
2    FROM   user_source
3   WHERE  UPPER(text) LIKE UPPER('%&KEYWORD%');
Enter value for keyword: Testing
old   3:  WHERE  UPPER(text) LIKE UPPER('%&KEYWORD%')
new   3:  WHERE  UPPER(text) LIKE UPPER('%Testing%')

TYPE         NAME                                 LINE
------------ ------------------------------ ----------
PROCEDURE    P_TEST                                  4

Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html