Oracle really spoils you with the ALL_DEPENDENCIES data dictionary view. If you want to know what depends on what, you just query it. These days, I’m working in Sybase again, so I had to figure out how to do it using Sybase’s data dictionary views. Hopefully you’re looking for the same thing and this will be useful to you:
SELECT o.id, o.name, o.type FROM sysobjects AS o, sysobjects AS do, sysdepends AS d WHERE do.name = 'procedureName' AND d.depid = do.id AND o.id = d.id
Just replace 'procedureName' with the name of the procedure you’re interested in.