Oracle/PowerBuilder dll connection issues

We are having very strange problems using oracle 9i and the powerbuilder oracle drivers. Certain sql statements are retrieving several records and then hanging while oracle drops the session. I have started a thread about this a long time ago but I got no where with it. It contains logs and more info though: http://groups.google.com/group/comp.databases.oracle.misc/browse_frm/thread/c0adea9871bc2eab?hl=en

The two servers we access and have had a problem with are accessed over a VPN. I don't think this problem has anything to do with loose cables or a firewall or anything though for two reasons:

1. The queries that give us problems select specific columns and if I make the slightest change to the column list, it works fine. For example, one query that is currently giving me this problem is: SELECT "STAFF"."NAME_LAST",
   "STAFF"."NAME_FIRST",
   "STAFF"."LOGIN_ID",
   "STAFF"."STAFF_ID",
   "STAFF"."STATION_EXTENSION",
   "STAFF"."STATUS",
   "STAFF"."ACTIVE",
   "STAFF"."TITLE"
   FROM "STAFF" while even the addition of a static column fixes it: SELECT "STAFF"."NAME_LAST",
   "STAFF"."NAME_FIRST",
   "STAFF"."LOGIN_ID",
   "STAFF"."STAFF_ID",
   "STAFF"."STATION_EXTENSION",
   "STAFF"."STATUS",
   "STAFF"."ACTIVE",
   "STAFF"."TITLE",
   ' ' FROM "STAFF"
2. If I run the same query from another utility such as Toad or SQLPlus, it works fine, but running it through the PowerBuilder ide or any application created using PowerBuilder results in this problem.

These problems seem to have to do with both the specific records being selected, and the specific columns being selected. #1 above shows what I mean about columns and I will give you an example of what I mean with records: through a process of trial and error (deleting rows and putting them back) we were able to determine that the statement above fails when retrieving 1 specific row (it retrieves about 2100 out of the 2306 records in the staff table before hanging. I assume the next row it would retrieve would be the one we identified because all rows are retrieve if I delete that one.)

I tried a few more things with the problem record from the example above: I backed up the row, along with 6 other rows, to a temp table (T_STAFF1) and could select from it fine. I then backed those same rows up to another temp table (T_STAFF2) and ran the following: <B>insert into t_staff2 SELECT * FROM STAFF WHERE STAFF_ID NOT IN (SELECT STAFF_ID FROM T_STAFF2) which inserted 2299 records and I was still able to select from T_STAFF2 fine. I then deleted the bad row from T_STAFF2 and reinserted it into T_STAFF2 from T_STAFF1 and then hit the problem again when selecting from T_STAFF2.

If none of this seems to make any sence, that's because the problem makes no sence. I really hope someone knows what this problem is and/or can help me out since we've run into this problem off and on for a long time and it's very annoying!

Thanks,
Dan Received on Mon Sep 11 2006 - 16:37:42 CDT