Thanks for the question, Dexter.
Asked: October 09, 2023 - 6:26 pm UTC
Last updated: October 10, 2023 - 2:09 pm UTC
Version: 19.4
Viewed 1000+ times
You Asked
I'm after a list (text to go into Excel) of dependencies for a specific package.
With the package open in SQL Developer (22.2.0.173), click on the Dependencies tab and you get nice list of dependencies for the package. How can one obtain the SQL used to generate this list?
I've tried in vain to query all_dependencies, sys.all_objects, etc. views, but no joy.
Thank you!
Answer (one version):
With the Dependencies tab selected, right click in the results grid and choose Save Grid as Report... from the pop-up menu. Copy the SQL from the SQL window of the Create Report box that appears and click the Cancel button to close this window. Paste the SQL into a new window in SQL Developer.
Return to the Dependencies tab of the package and now choose the Details tab. Copy the Object_ID value from this window - it will be used in the next step. Switch to the window you pasted the generated SQL into and run the code. An Enter Binds dialog should appear.
Paste the Object_ID value into the Value box and clear the Null checkbox. Voila!
I tweaked the query to not generate a link in the Name column, but that's my preference.
Hope this helpful to someone else.
and Chris said...
I'm unclear - are you asking us what the query is or telling us what it is? :)
The SQL dev query is based on public_dependency which joins to the *_objects, excluding sys/system objects. It uses a hierarchical query to find all the recursive dependencies.
You can get similar information by querying *_dependencies.
We're not taking comments currently, so please try again later if you want to add a comment.