Searching for Text within Oracle Stored Procedures
Solution 1
SELECT * FROM ALL_source WHERE UPPER(text) LIKE '%BLAH%'
EDIT Adding additional info:
SELECT * FROM DBA_source WHERE UPPER(text) LIKE '%BLAH%'
The difference is dba_source will have the text of all stored objects. All_source will have the text of all stored objects accessible by the user performing the query. Oracle Database Reference 11g Release 2 (11.2)
Another difference is that you may not have access to dba_source.
Solution 2
I allways use UPPER(text)
like UPPER('%blah%')
Solution 3
If you use UPPER(text)
, the like '%lah%'
will always return zero results. Use '%LAH%'
.
Comments
-
PseudoToad almost 2 years
I need to search through all of the stored procedures in an Oracle database using TOAD. I am looking for anywhere that the developers used MAX + 1 instead of the NEXTVAL on the sequence to get the next ID number.
I've been doing SQL Server for years and know several ways to do it there but none are helping me here.
I've tried using
SELECT * FROM user_source WHERE UPPER(text) LIKE '%blah%'
Results are returned but only for my default schema and not for the schema I need to be searching in.
I also tried the below but it just errors
SELECT * FROM SchemaName.user_source WHERE UPPER(text) LIKE '%blah%'
-
PseudoToad over 13 yearsYou rock! Thanks much Shannon!
-
Nils Weinander over 13 yearsIf you want to limit the search to a specific schema, you can add AND OWNER = 'SCHEMA OWNER NAME'.
-
Baum mit Augen over 7 yearsTo critique another answer, please use a comment. (Requires 50 reputation which you can earn by posting useful answers and questions.)