SQL to search objects including stored procedures in Oracle

Question

I need to write some sql that will allow me to query all objects in our Oracle database   Unfortunately the tools we are allowed to use don t have this built in  Basically  I need to search all tables  procedures  triggers  views  everything   I know how to search for object names   But I need to search for the contents of the object  i e  SELECT   FROM DBA OBJECTS WHERE object name     search string     Thanks  Glenn

User · Answer

i reached this question while trying to find all procedures which use a certain table

Oracle SQL Developer offers this capability, as pointed out in this article : https://www.thatjeffsmith.com/archive/2012/09/search-and-browse-database-objects-with-oracle-sql-developer/

From the View menu, choose Find DB Object. Choose a DB connection. Enter the name of the table. At Object Types, keep only functions, procedures and packages. At Code section, check All source lines.

User · Answer

In Oracle 11g  if you want to search any text in whole database or procedure below mentioned query can be used  select   from user source WHERE UPPER text  LIKE   YOUR SAGE

User · Answer

ALL SOURCE describes the text source of the stored objects accessible to the current user   Here is one of the solution  select   from ALL SOURCE where text like   some string

User · Answer

I would use DBA SOURCE  if you have access to it  because if the object you require is not owned by the schema under which you are logged in you will not see it   If you need to know the functions and Procs inside the packages try something like this   select   from all source  where type    PACKAGE     and  upper text  like   FUNCTION   or upper text  like   PROCEDURE       and owner     SYS     The last line prevents all the sys stuff  DBMS  et al  from being returned  This will work in user source if you just want your own schema stuff

User · Answer

I m not sure I quite understand the question but if you want to search objects on the database for a particular search string try   SELECT owner  name  type  line  text  FROM dba source WHERE instr UPPER text   UPPER  srch str    gt  0    From there if you need any more info you can just look up the object   line number

User · Answer

i m not sure if i understand you  but to query the source code of your triggers  procedures  package and functions you can try with the  user source  table    select   from user source

[sql] SQL to search objects, including stored procedures, in Oracle

Examples related to sql

Examples related to oracle

Examples related to search

Examples related to full-text-search