How to search Oracle system objects for a reference

I previously posted on how to search stored procedures in Microsoft SQL Server for a particular reference such as a table or field. Here is how to do the same thing in Oracle PL/SQL.

Oracle has a view called ALL_SOURCE which contains the source of all system objects the currently logged in user has access to. This view contains the fields owner, name, type, line, and text.
Owner is the owner of the object.
Name is the obviously the name of the object.
Type is the type of object which can be one of the following FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE or TYPE BODY.
Line is the line number of the text in the source.
Text is the actual text of the source.

So to search for the text “support” we would use this select statement:
select *
from sys.ALL_SOURCE
where text like '%support%'

Share and Enjoy:
  • Print
  • Digg
  • StumbleUpon
  • Facebook
  • Twitter
  • Google Bookmarks

Leave a Reply

Your email address will not be published. Required fields are marked *