I have written a Java stored procedure under Oracle 8.1.7 that is to return an XML representation of of an application configuration stored in a tyable. The java return type of the method is String, which maps to the Oracle type of LONG in the corresponding PL/SQL function.
I call this via JDBC's CallableStatement interface. I set the output parameter to LONGVARCHAR using the registerOutParameter.
This works fine for a resulting XML string of up to about 4150 chars. Any longer and I get "A truncation or null fetch error occured" when the execute() method of CallableStatement is called.
It was my understanding that the LONG type can hold a string up to 2GB or so. Where is this truncation occurring? Is there a better way of accomplishing this?
Apparently there is a bug in 8i that truncates all stored function return values to 4k or so. I changed to using a stored procedure with an OUT param and seems to be working fine. I'm amazed that it took me about a 1.5 days to stumble onto this known bug. Arggg...