Re: partially filled bind arrays?

From: Volker Hetzer <volker.hetzer_at_ieee.org>
Date: Wed, 15 Mar 2006 21:15:08 +0100
Message-ID: <dv9skc$bgi$1_at_nntp.fujitsu-siemens.com>

DA Morgan schrieb:

> Volker Hetzer wrote:

>> The problem I am facing happens when I am programming a client. I use
>> VBscript and the OO4O driver from oracle. The bind array gets
>> allocated on the client side (OraDatabase.Parameters.AddTable(...)).
>>
>> Now, when I don't know the number of rows in advance, I create
>> a bind array of, say 1000 rows. I then read from a stream and whenever
>> I've read 1000 rows I call Statement.Refresh.
>>
>> The problem arises when the stream ends. At that point I may have ten
>> or maybe 817 rows in the bind arrays, with the remaining rows
>> containing data
>> from the previous 1000 rows. I can't assign Empty or Null to those rows
>> and I can't resize the bind array.
>>
>> So, data gets inserted twice.
>> If possible I'd like to avoid storing the data separately and copying it
>> into a made-to-measure bind array.
>>
>> Btw, I've created a TAR for this and if I get something useful I am sure
>> I'll post it here. I am hopeful for a solution because sql*loader faces
>> the same problem and apparently seems to manage.
>>
>> Lots of Greetings!
>> Volker

> 
> Take a look at the stuff on my Bulk Binding page. Notice the use of 
> functions such as .FIRST, .LAST, .NEXT, .PRIOR, and for where you seem
> to be heading .COUNT.
> 
> So for example I can insert this way:
> 
> FORALL i IN 1..l_data.COUNT
> INSERT INTO child VALUES l_data(i);
> 
> I don't need to know the count.
> 
> Also look at the example following the heading: Sparse Collection Demo

Unfortunately the client collections are never sparse. You allocate them with a certain capacity and that's what count returns. If they are not filled properly, things go wrong. Doing what you suggest would imply lots of statements and lots of network traffic. That's what I am hoping to avoid. I'll see what support tells me. If they can't help me I will need to keep the data in a separate buffer until I've got enough data for the parameter array and do separate bind arrays for the odd remainder of the data.

Lots of Greetings and thanks!
Volker Received on Wed Mar 15 2006 - 21:15:08 CET