suhinrasheed
asked on
How to find all Referenced Oracle Objects from Head to Tale
Hi,
Could anyone share with me a way -- preferably a script which could help me to get the below information.
Say i have a Procedure or package or anyother stored subprogram(Also apart from stored programs views and materialized views),i need a script say to which i can pass this Procedure/Package/View etc name and then the code should give me the data results as which all other stored subprograms are used within the particular stored program name which we mentioned in our WHERE or Predicate clause.
Regards,
Suhin
Could anyone share with me a way -- preferably a script which could help me to get the below information.
Say i have a Procedure or package or anyother stored subprogram(Also apart from stored programs views and materialized views),i need a script say to which i can pass this Procedure/Package/View etc name and then the code should give me the data results as which all other stored subprograms are used within the particular stored program name which we mentioned in our WHERE or Predicate clause.
Regards,
Suhin
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select * from sys.ALL_SOURCE
Sorry,I mean select * from sys.ALL_SOURCE
WHERE owner = 'SCOTT' AND
object_name = 'MY_PACKAGE' AND
object_type = 'PACKAGE'
WHERE owner = 'SCOTT' AND
object_name = 'MY_PACKAGE' AND
object_type = 'PACKAGE'
from user_dependencies
where name ='MYPACKAGE'
AND OWNER ='SCOTT';
You can run the above in toad to see all the dependent objects. NOTE: This cannot show all the objects used in dynamic sql. Only objects which are referenced in static sql are shown.