Using java to move large objects/data over db link?

Hi;

I am constrained to use a database link; my java webapp uses its own Oracle 9i database, which connects to a second Oracle 9i database for certain data. Connecting directly to the second database is not an option.

The limitations of moving data over the link are well documented; you can't use the standard approach of returning a cursor and using it like a result set, you can't return LOBs...

I have successfully built a Serializable object that moves data over the link, but it is constrained in how much data it can transmit at one time. My Oracle Stored Function returns data of type LONG RAW, and the java class is returning a byte[]. Currently, it can return approx 230 rows of a table who's field descriptors total 140 bytes for a total of approx. 32k bytes. This limitation makes sense, given the stuctural limit of 32k bytes for things like varchars and packet sizes.

Does anyone have any ideas about how to get around these limitations? We are using XML in some cases, but this approach also has its limitations. I don't have CREATE privileges on othe target DB (so I can't create Oracle objects) and I'm not sure I could convince the DBAs that this is desirable...

thanks in advance!

Bill Spornitz
Portal Developer
University of Manitoba Received on Wed Sep 13 2006 - 10:32:58 CDT