Diff results Toad Debug vs Compiled package

I’ve received a couple of reports from my Production users of an issue where a query against a DBLink fails to notice that a vendor in the linked DB doesn’t exist. The PL/SQL code is in a packaged stored procedure.

When I try the code in Toad Debugger (formerly ver 10.5 and now 10.6) I get a different result vs when I run in VB.NET 6.0 debugger (against the compiled package).

Here are the details:

I’m Running an Oracle packaged procedure that queries a remote DB via a DBLink. The query checks for a Vendor in an AP/GL package. If the vendor isn’t found, a record is inserted into an error table.

In Toad Debugger, whether I step through the code or just let it rip, it works great. If I switch immediately thereafter to my VB application, attached to the very same database, using the very same test data, the process fails to write anything into the error table. Oracle throws no errors.

I’ve added debug code that logs messages to a system error table before and after each INSERT INTO statement in the procedure, and the results indicate that the procedure is indeed running. I’m just not getting anything back from the SELECT part of the INSERT INTO.

Oddly, I have a second test environment where the compiled package code works fine. Can anyone offer some advice?

Mark Baran
Sr. analyst developer
American Agricultural Insurance Co