JIT debugging: Debugging with 3rd party application and Toad.
1. Connect the external application to the database. In the external application code, add these lines your code before the call to the SQL script/procedure. Note: This Session ID is determined by the user.
ALTER SESSION SET PLSQL_DEBUG=TRUE;
id := DBMS_DEBUG.INITIALIZE('TOAD_EXAMPLE');
OPEN l_cursor FOR SELECT * FROM scott.emp WHERE empno = 7369;
2. In Toad, connect to the same database instance as the external application. Display the code to debug in the Procedure Editor. Set a break point if you want the debugger to halt in the SQL code.
3. In Toad, from the Debug menu select Attach | External Session.
4. Enter the same Session ID, in the Debug Input ID window passed to or returned by the initialize statement.
In the example here, the Session ID is 'TOAD_EXAMPLE'. Toad waits for the application to execute PL/SQL code.
5. From the application, execute the PL/SQL block that calls the stored program to be debugged. This will run through you external application code and run through the alter session declaring statements enabling the debug session for Toad.
a. Your external application may appear to be hung; but what it is waiting for Toad to finish. Inside Toad, the debugger should have run through the SQL code.
b. In the Toad Procedure Editor, it should stop at any set breakpointst. You can continue to debug in Toad.
6. Verify when the execution of the PL/SQL code is complete. Toad displays the message: "Execution complete" and the external application continues its normal work.