How can I inspect query results in a stored PL/SQL function WHILE they're being executed by an external application?

Question

I'm hitting an issue where a query in a stored procedure appears to be deleting more rows than it should be. This query uses a sub-select to determine which records to delete. For example:

DELETE FROM MyTable_gt mt
WHERE mt.Id not in (SELECT ot.Id
                      FROM OtherTable_gt ot);

This procedure is being called by a C# application, and the tables I'm working with are global temporary tables. Therefore, I need some way to be able to inspect the query results while it's being executed from my application.

I finally found the Oracle Developer Tools (ODT) for Visual Studio, so I installed that thinking that it could help to be able to debug through the code as it's happening. However, now I realize that it really doesn't help me at all because I still have no way of knowing which Ids the sub-select is returning.

What I'm looking for is some way to be able to see exactly what that sub-select is returning. I'd even be happy just with some way to run queries within that transaction's context (like through the Immediate Window in Visual Studio). However, I've been unable to find a way to do this.

Is there any tool out there that would help with this? Or am I stuck with just manually looping through query results and printing them out? (I thought that us programmers had moved past using print debugging)

Answer 1

Any DELETE statement can be a SELECT statement and you could insert your data in other tables where you're not restricted by the session commit/rollback. PLSQL Developer has Test windows and you could debug PLSQL, but personally I don't find it easier than print debugging.