CODEWITHSUNDEEP
sqloracle-databasestored-proceduresfull-text-searchtoad
PseudoToad
PseudoToad

Reputation: 1574

Searching for Text within Oracle Stored Procedures

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%'

Upvotes: 51

Views: 235811

Answers (4)

skvp
skvp

Reputation: 2000

For me, the given query didn't work. It was showing no result. I really don't know why. But the "dependency" feature of SQL Developer saved my day!!!.

In SQL Developer, when you select the table in the left-hand side "connection" view, tables details are opened in the "document" view on the right-hand side. There are many tabs in document view like columns, data, model, constraints, etc. One of the tabs is "Dependencies". This tabs list all the objects like triggers, indexes, functions, procedures, etc. where the table is referenced.

For TOAD, I think, it is "Referential" and "Used By" tabs. (Not sure about it, please refer to TOAD reference materials.)

Hope this will help someone who is struggling with query like me.

Upvotes: 0

Guest1
Guest1

Reputation: 31

I always use UPPER(text) like UPPER('%blah%')

Upvotes: 3

Shannon Severance
Shannon Severance

Reputation: 18410

 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.

Upvotes: 103

ANM KABIR
ANM KABIR

Reputation: 37

If you use UPPER(text), the like '%lah%' will always return zero results. Use '%LAH%'.

Upvotes: 2

Related Questions