Oracle ODBC (Native) and Visual Basic ADO 2.5 Timeouts and Hanging Clients

We have a program using Visual Basic ADO 2.5 with Oracle ODBC Native for 8.1.7 and 8.1.6 connecting an Oracle 8.1.7.4 database. In the field, many Visual Basic clients upload data via Oracle ODBC to a central database. The network we are finding out is not the best. Intermittently we have network blips, although the network folks have been unable to find anything. What we see is the following:

Intermittently connections and commands to the database will hang and wait for a TCP/IP response back. We never see a session still residing on the database. Indicating that the session went inactive on the db and was cleaned up by Oracle, but the client never receives an error, meaning Net8 or ODBC never raises an error.

We have reproduced this in a very hokey way. We coded up a vb client and a stored proc. The vb client calls the proc, and the proc loops until we insert a record into table. At the exact same time the record is inserted we pull the network connection out of the client for a split second and put it back in. Presumably, the stored proc returns a response to the vb client, but b/c the client is off the network, the packet is lost. As a result, the client hangs and no error is raised. Yesterday, Oracle Tech Support said this is by design, which is very bizarre.

We have obtained SQLNET logs for the clients hanging. The trace is at the bottom of the post.

We also have talked to Microsoft re: ADO 2.5. They have indicated that the network handle should occur at the ODBC level.

The hang can occur on a ado.connection open, or any type of ado.command execution. We use the Connection Timeout in ADO, but we do not see any errors raised from it - the connection open stills hang even with the connection timeout set. Recently we have changed to asynchronous processing on the ado.connection open, but we haven't seen the error again, so we don't know if it works. We have the Command Timeout property set that should presumably handle hangs on executes but this only works 9 out of 10 times. Sometimes a hung command will timeout and sometimes it won't.

If anyone has seen any behavior that is similar, I would appreciate any guidance or thoughts you would have on this. Oracle has been bouncing us between the Network group and the ODBC group, and they haven't given us much guidance.

Thanks for your help in advance,
Sara K. Koike

(1032) [21-NOV-2002 00:00:40] nspsend: 01 4E 00 00 06 00 00 00 |.N......|
(1032) [21-NOV-2002 00:00:40] nspsend: 00 00 11 69 0A 4C A1 C1 |...i.L..|
(1032) [21-NOV-2002 00:00:40] nspsend: 01 01 00 00 00 01 00 00 |........|
(1032) [21-NOV-2002 00:00:40] nspsend: 00 03 5E 0B 29 04 04 00 |..^.)...|
(1032) [21-NOV-2002 00:00:40] nspsend: 00 00 00 00 1C BB C1 01 |........|
(1032) [21-NOV-2002 00:00:40] nspsend: 9A 00 00 00 10 05 C1 01 |........|
(1032) [21-NOV-2002 00:00:40] nspsend: 0A 00 00 00 00 00 00 00 |........|
(1032) [21-NOV-2002 00:00:40] nspsend: 34 05 C1 01 00 00 00 00 |4.......|
(1032) [21-NOV-2002 00:00:40] nspsend: 01 00 00 00 00 00 00 00 |........|
(1032) [21-NOV-2002 00:00:40] nspsend: 5C BA C1 01 01 00 00 00 |\.......|
(1032) [21-NOV-2002 00:00:40] nspsend: 00 00 00 00 00 00 00 00 |........|
(1032) [21-NOV-2002 00:00:40] nspsend: 00 00 00 00 00 00 00 00 |........|
(1032) [21-NOV-2002 00:00:40] nspsend: 36 05 C1 01 FE 40 42 65 |6....@Be|
(1032) [21-NOV-2002 00:00:40] nspsend: 67 69 6E 20 55 50 44 41 |gin UPDA|
(1032) [21-NOV-2002 00:00:40] nspsend: 54 45 42 55 4C 4B 49 4E |TEBULKIN|
(1032) [21-NOV-2002 00:00:40] nspsend: 53 45 52 54 5F 30 30 32 |SERT_002|
(1032) [21-NOV-2002 00:00:40] nspsend: 5F 50 4B 47 2E 55 50 44 |_PKG.UPD|
(1032) [21-NOV-2002 00:00:40] nspsend: 41 54 45 42 55 4C 4B 49 |ATEBULKI|
(1032) [21-NOV-2002 00:00:40] nspsend: 4E 53 45 52 54 5F 30 30 |NSERT_00|
(1032) [21-NOV-2002 00:00:40] nspsend: 32 5F 50 52 43 28 27 4D |2_PRC('M|
(1032) [21-NOV-2002 00:00:40] nspsend: 4B 45 54 48 27 2C 40 27 |KETH',@'|
(1032) [21-NOV-2002 00:00:40] nspsend: 4D 31 45 36 32 55 39 30 |M1E62U90|
(1032) [21-NOV-2002 00:00:40] nspsend: 52 27 2C 27 32 30 2F 31 |R','20/1|
(1032) [21-NOV-2002 00:00:40] nspsend: 31 2F 32 30 30 32 20 32 |1/2002 2|
(1032) [21-NOV-2002 00:00:40] nspsend: 33 3A 35 35 3A 31 32 27 |3:55:12'|
(1032) [21-NOV-2002 00:00:40] nspsend: 2C 27 30 2E 30 35 27 2C |,'0.05',|
(1032) [21-NOV-2002 00:00:40] nspsend: 27 31 30 33 27 2C 27 36 |'103','6|
(1032) [21-NOV-2002 00:00:40] nspsend: 5F 54 53 54 27 2C 27 30 |_TST','0|
(1032) [21-NOV-2002 00:00:40] nspsend: 31 2F 30 31 2F 32 30 1A |1/01/20.|
(1032) [21-NOV-2002 00:00:40] nspsend: 39 39 20 30 30 3A 30 30 |99 00:00|
(1032) [21-NOV-2002 00:00:40] nspsend: 3A 30 30 27 2C 20 3A 72 |:00', :r|
(1032) [21-NOV-2002 00:00:40] nspsend: 73 65 74 29 3B 20 45 6E |set); En|
(1032) [21-NOV-2002 00:00:40] nspsend: 64 3B 00 01 00 00 00 01 |d;......|
(1032) [21-NOV-2002 00:00:40] nspsend: 00 00 00 00 00 00 00 00 |........|
(1032) [21-NOV-2002 00:00:40] nspsend: 00 00 00 00 00 00 00 00 |........|
(1032) [21-NOV-2002 00:00:40] nspsend: 00 00 00 00 00 00 00 08 |........|
(1032) [21-NOV-2002 00:00:40] nspsend: 00 00 00 00 00 00 00 04 |........|
(1032) [21-NOV-2002 00:00:40] nspsend: 00 00 00 01 66 01 00 00 |....f...|
(1032) [21-NOV-2002 00:00:40] nspsend: 01 00 00 00 00 00 00 00 |........|
(1032) [21-NOV-2002 00:00:40] nspsend: 00 00 00 00 00 00 00 00 |........|
(1032) [21-NOV-2002 00:00:40] nspsend: 00 00 00 00 00 00 00 00 |........|
(1032) [21-NOV-2002 00:00:40] nspsend: 07 04 00 00 00 00 00 00 |........|
[21-NOV-2002 00:00:40] nspsend: normal exit
[21-NOV-2002 00:00:40] nsdofls: exit (0)
[21-NOV-2002 00:00:40] snsbitts_ts: entry
[21-NOV-2002 00:00:40] snsbitts_ts: acquired the bit
[21-NOV-2002 00:00:40] snsbitts_ts: normal exit
[21-NOV-2002 00:00:40] nsdo: nsctxrnk=0
[21-NOV-2002 00:00:40] snsbitcl_ts: entry
[21-NOV-2002 00:00:40] snsbitcl_ts: normal exit
[21-NOV-2002 00:00:40] nsdo: normal exit
[21-NOV-2002 00:00:40] nsdo: entry
[21-NOV-2002 00:00:40] nsdo: cid=0, opcode=85, *bl=0, *what=0, uflgs=0x0, cflgs=0x3
[21-NOV-2002 00:00:40] snsbitts_ts: entry
[21-NOV-2002 00:00:40] snsbitts_ts: acquired the bit
[21-NOV-2002 00:00:40] snsbitts_ts: normal exit
[21-NOV-2002 00:00:40] nsdo: rank=64, nsctxrnk=0
[21-NOV-2002 00:00:40] snsbitcl_ts: entry
[21-NOV-2002 00:00:40] snsbitcl_ts: normal exit
[21-NOV-2002 00:00:40] nsdo: nsctx: state=8, flg=0x420d, mvd=0
[21-NOV-2002 00:00:40] nsdo: gtn=127, gtc=127, ptn=10, ptc=2023
[21-NOV-2002 00:00:40] snsbitts_ts: entry
[21-NOV-2002 00:00:40] snsbitts_ts: acquired the bit
[21-NOV-2002 00:00:40] snsbitts_ts: normal exit
[21-NOV-2002 00:00:40] snsbitcl_ts: entry
[21-NOV-2002 00:00:40] snsbitcl_ts: normal exit
[21-NOV-2002 00:00:40] nsdo: switching to application buffer
[21-NOV-2002 00:00:40] nsrdr: entry
[21-NOV-2002 00:00:40] nsrdr: recving a packet
[21-NOV-2002 00:00:40] nsprecv: entry
[21-NOV-2002 00:00:40] nsprecv: reading from transport...
[21-NOV-2002 00:00:40] nttrd: entry