XML to CLOB to Oracle stored proc using JDBC is too slow

We have a system in which many (say, 100) database calls are made in succession in order to insert a "complete" set of data relating to a particular customer. It's too slow, taking 30 seconds or more.

So we thought: let's cut the number of database calls from 100 to 1 by gathering all the data into an XML document, and parsing it on the database side using Oracle 9i's new XML capabilities in a stored procedure.

The first problem was that we couldn't figure out how to pass a CLOB as a parameter to a stored procedure. We resorted to (1) inserting a record with an empty clob into a table created for this purpose, (2) getting a JDBC CLOB locater by doing a select on that table, then (3) passing the CLOB locator into the stored procedure. (see http://oracle.cs.uoi.gr/enterprise_server/java.815/a64685/oraext4.htm#1043351). Three steps instead of one. Is there a better way?

Well, we got it working and discovered that the transaction takes 90 seconds instead of 30. We believe this is due to inefficient data chunking as Oracle reads the CLOB chunk by chunk. Anyone know a solution to this? Received on Tue Sep 03 2002 - 19:37:02 CDT