John, Thanks for your incites. I tried what you said.
I read up on NVARCHAR2 v VARCHAR2 - interesting. I also see that Oracle has a way (more than one way) to specify if a VARCHAR2 should contain bytes or characters - further while a VARCHAR2(11 byte) and a VARCHAR2(11 char) are different (the second can have as many as 4 times as many bytes in it as the first) VARCHAR2(4000 byte) and VARCHAR(4000 char) are not different - 4000 bytes is the max for either - no matter the size of a character. Unicode makes everything harder. "CHAR" may mean "BYTE" "2 BYTES" or "4 BYTES". And client and server have to agree. I tried this: I uses SUBSTRB(field,1,NNNN) to truncate the actual bytes coming from oracle back to the client. Results are a bit odd. For 3999 no change. For 2000 all errors disappear. For 3000 - some cases that used to error now succeed - but some cases that used to succeed now fail. I also played with "matching up" the inner select and the outer - which isn't really very valuable - the client never sees the data from the inner select - and I only added the outer select so I could select by row number (which I couldn't do on the inner select since "rownum" is a pseudo column). The outer select is basically the interface that OCI sees. Specifically cast(SUBSTRB(field,1,2000) as VARCHAR(4000)) errors out exactly the same way as no substrb(). For the 2000 byte case I used cast(SUBSTRB(field,1,2000) as VARCHAR(2000 byte)). For the 3000 byte case I used cast(SUBSTRB(field,1,3000) as VARCHAR(3000 byte)). I don't think truncating to 2000 bytes is a solution, but I suppose I could try breaking the field into 2 2000 byte strings (or 4 1000 bytes strings) and recombine them in some other part of the code. I would need to consider the best way to do that - perhaps a output column naming convention. I ran all of my variations on the old server and the new server and nothing failed on the old server. Too much output to send it all to the list - if anyone wants something specific I can send it. My money is still on a wild pointer or similar. I looked thru the DBI log and found the following various field rc error codes (sorted): field #3 with rc=12851(UNKNOWN RC=12851)) field #3 with rc=12854(UNKNOWN RC=12854)) field #3 with rc=20041(UNKNOWN RC=20041)) field #3 with rc=25934(UNKNOWN RC=25934)) field #3 with rc=26962(UNKNOWN RC=26962)) field #3 with rc=48(UNKNOWN RC=48)) field #3 with rc=83(UNKNOWN RC=83)) field #4 with rc=1280(UNKNOWN RC=1280)) field #4 with rc=12870(UNKNOWN RC=12870)) field #4 with rc=14128(UNKNOWN RC=14128)) field #4 with rc=17230(UNKNOWN RC=17230)) field #4 with rc=18688(UNKNOWN RC=18688)) field #4 with rc=24919(UNKNOWN RC=24919)) field #4 with rc=25196(UNKNOWN RC=25196)) field #4 with rc=25926(UNKNOWN RC=25926)) field #4 with rc=26691(UNKNOWN RC=26691)) I am not really sure which goes with what case - but I am really not thinking that the codes are real oracle error codes - but junk data which is a symptom of a problem somewhere else. I could modify the tests to clear out the log and capture the "UNKNOWN RC" codes for each, but I would rather to more digging on the original case I reported to this list. Brian Fennell
