Oralce OleDb vs. SQL*Plus -- Can't make it work -- help!

One of our vendors has given a set of SQL commands that need to be run in sequence against our Oracle 8.1 server. One choice was to create a text file with the SQL commands and "pipe" them into SQL*Plus. I decided that we'd have more control over this process (e.g. determining where potential errors are generated), by instead using Oracle's OleDb driver. This programmatic approach is working great except for some strange results with respect to its last step -- the invocation of a stored procedure in the database (supplied by this vendor and which actually invokes some Java code).

The purpose of this procedure is to generate XML for a number of documents stored in a table in the database. The procedure takes a few args and one of them is DOCS_TO_COMMIT defined as "the number of documents to process before committing". To commit once for all documents, use -1 for DOCS_TO_COMMIT". If we pass -1, we get the expected number of total documents at the end of the procedure's processing. If, however, we pass some other value -- say 10 -- we end up with only the first 10 documents appearing final results table. What's strange, is that if we pass 10 using SQL*Plus, we get the all the documents. So there is something different in the two environments and I can't put my finger on it. I thought it might have something to do with transaction isolation levels, but none of the permutations of those seems to make a difference. Any help would be appreciated because if we can't get to the bottom of it I'm going to have to abandon OleDb and go with a script/SQL*Plus approach. I can post some code if someone is interested.

TIA P.S. I can't encapsulate the entire process as a stored procedure because there is some DML that has to be done as one user and then the procedure in question has to be invoked by SYS. Received on Wed Oct 02 2002 - 09:56:42 CDT