RE: Hunting down (possible) memory leak in DBD::Oracle

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