CODEWITHSUNDEEP
oracle-databasefunctionsearchstored-procedurestriggers
André
André

Reputation: 25584

How to search strings inside Oracle Procedures, Functions and Triggers?

I need to find strings inside Oracle Procedures, Functions and Triggers.

For SQLServer I use something like this:

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%m4_plf_par_periodo%'

There is something like this for Oracle?

Give me a clue.

Best Regards,

Upvotes: 1

Views: 21381

Answers (3)

Jason
Jason

Reputation: 101

Use the "Find Database Object" wizard in free Oracle DB tool "SQL Developer".

To be exact, download and install Oracle SQL Developer from Oracle --> Create a new connection to the database, using a power-user --> In the SQL Developer menu choose "View" --> open menu item "Find DB Object" --> opens a Find Database Object Widget on the left panel --> Choose the DB connection --> Select specific schemas to search --> Select "All Source Lines" node --> type a string to search --> Click Go.

Expect it to do a non-cases-sensitive wild-card search in all source of Triggers/Procedures/Functions/Packages, owned by the selected schema and display a comprehensive search report of sample lines of code from each object where it was located.

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132700

See this question for the solution using USER_SOURCE.

Upvotes: 1

Vincent Malgrat
Vincent Malgrat

Reputation: 67782

you can query ALL_SOURCE (contains the source to all programs you have access to).

Alternatively, DBA_SOURCE describes the text source of all stored objects in the database and USER_SOURCE contains the text source of your stored objects only.

Upvotes: 3

Related Questions