Link to home
Start Free TrialLog in
Avatar of mikensu12
mikensu12

asked on

data dictionary

Want to search packages, views, table, functions, etc for an object name 'company_search' . What is the query from data dictionary?

Thanks
Avatar of rmaggarw
rmaggarw

select * from all_objects where object_name = 'COMPANY_SEARCH';
select * from all_objects where OBJECT_NAME = 'company_search'
Avatar of slightwv (䄆 Netminder)
Are you asking if the object is used in source code?

There is a DBA_DEPENDENCIES view:
select owner, name, type from dba_dependencies where referenced_name='COMPANY_SEARCH';

That will work if the procedure code doesn't use dynamic SQL.  IF if might then:
select name from user_source where upper(text) like '%COMPANY_SEARCH%';
ASKER CERTIFIED SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
i will second johnsone comment that while searching, you should query DBA_OBJECTS view.

So can you try the below 2 queries and you should get some output if such object exists from one of the queries.

select * from DBA_OBJECTS where object_name = 'COMPANY_SEARCH';  

--> this will help to get you the object if it is created with just company_search as the object name without quotes enclosing the name.

or

select * from DBA_OBJECTS where object_name = 'company_search';  --> this will help to get you the object if it is created with "company_search" as the object name with quotes enclosing the obect name to preserve its exact name in lower case letters.
This data is recorded in the systemtables and a full list can be found at:
http://www.techonthenet.com/oracle/sys_tables/
The techonthenet link above is  is not a full list of the dictionary tables.
In fact those aren't tables at all, but views.

It is however, as stated in the link, a set of commonly used views.
However the critical views have already been mentioned above.
if you're looking for a procedure called "company_search"  you can look in dba_procedures/all_procedures to see where it is defined whether it is a stand alone procedure or defined within a package.