What is the best way to debug an Oracle SP?

Question

I am sort of new with Oracle. We are having lot of issues.

We use two main clients for running queries. SqlDeveloper (provided by Oracle) and PL/SQL Developer 7.1.

We are trying to debug a stored procedure. Problems are:-

1. There are some Stored Procedures that are compiled with somename.SPNAME meaning they are in a different schema/or synonym? So when we login to our DB from SQLDeveloper we are not able to see the stored procs that were compiled with somename.SPNAME.
2. When we login to the Same DB via PL/SQL developer we ARE able to see above mentioned SP's however, we are not able to debug anything. Clicking on Debug toolbar shows everthing is grey'ed out (except setting/modifying breakpoints).

So from here, we need to resolve either of the issues. Either change something in sql developer so all the SP's are shown and then we can debug them (since we know debugger in sql developer works) OR make debugging work in PL/SQL developer.

I know the question and explanation got a little verbose but we are struggling with this problem and could really use some help.

Answer 1

1. For Oracle SQL Developer, you'll see other schema's objects by expanding the "Other Users" node.

2. For debugging with PL/SQL Developer, you'll need the "DEBUG CONNECT SESSION" privilege.