Debug Oracle procedures called from VB.Net

Toad for Oracle
1

Hi,

I am trying to “Attach an External Session(from VB.Net) for Debugging” using Toad to debug an oracle function.

This works from one TOAD instance to another using the following commands

Pre-req. The function is compiled with debug.

Toad Session#1

select dbms_debug.initialize() from dual;

<>
exec dbms_debug.debug_on;

Toad Session #2

Then using the menu Debug|Attech external session using the <>

Toad Session #1

select f_pulse from dual

This then brings up Toad Session#2 where I can debug the function f_pulse.

However, when I do the steps completed by Toad Session#1 in VB.Net, the function executes, returning a value without Toad Session#2 debugging the function.

Does anyone know what I am doing wrong?

Thanks

Martin

2

Did you add the required oracle call to your vb code ???

3

cmd.Dispose()

But the DB connection is still allive.

I suspected the oracle debug was losing the ‘instance’ because of this, so I then issued each oracle debug step re-using the same command object, but this still didnt work.

for every individual database call. when each command is complete the object is disposed off.

yes I have tried several ways.

Ordinarily our database connection creates “command” objects

Dim cmd As OracleClient.OracleCommand = New OracleClient.OracleCommand(strSQL, objDBCon)

4

My question is did you add the required three lines of code to your vb app (see
red line below) in order for external debug to work:

From the Toad help:

Requirements for attaching to an external application

Before Initializing

Before initializing the debugger on the external session, disable server output
by issuing the ‘set serveroutput off’ command. If server output capture is
enabled, Oracle will freeze on calls to the DBMS_OUTPUT package so that these
calls can be debugged. This will give the displayance that the application has
frozen for no particular reason.

Initializing

To initialize Debug mode, the external application must execute three commands:

ALTER SESSION SET PLSQL_DEBUG=TRUE

id := dbms_debug.initialize(‘TOAD’)

dbms_debug.debug_on;

where TOAD can be replaced by any ID string. If this parameter is omitted, the
return value of initialize will be used as the Session ID. This ID string also
must be entered into Toad from Debug menu | Attach External Session.

The ALTER SESSION command (SQL) should be executed separately, while
dbms_debug.initialize and dbms_debug.debug_off (PL/SQL) can be placed in the
same PL/SQL block.

You can omit the ALTER SESSION command if you have all your procedures compiled
with debug.

After running external application

After the external application is finished executing the code that needs
debugging, it should execute the command:

dbms_debug.debug_off

Otherwise, all subsequent PL/SQL code that this application submits for
execution will be run in debug mode. This will cause the application to hang
until Toad attaches to it again.

5

I was not running the first line you mentioned

ALTER SESSION SET PLSQL_DEBUG=TRUE

but was running the other two lines from the VBapp. Interestingly, to run the thid command ( dbms_debug.debug_on;) I have to encapulate it into an oracle function, the command wont work directly from VB. I think this is ok, as thats how I proved it worked when debugging from another TOAD session.

I have added the first line as well, but TOAD is still not capturing the call to the procedure.