Toad World® Forums

Diff results Toad Debug vs Compiled package


#1

Message from: mbaran_375

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


Historical Messages

Author: mbaran_375
Date: Mon Sep 20 10:10:09 PDT 2010

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

__



#2

Martin,

it might be best to post this query to the main Toad list instead of
this one. More people monitor the TOAD list and this is more to do with
Toad than SQL.

Unfortunately, I’m unable to help with your problem. :frowning:

Cheers,
Norm. [TeamT]

Norman Dunbar
Contract Senior Oracle DBA
Capgemini Database Team (EA)
Internal : 7 28 2051
External : 0113 231 2051