Re: partially filled bind arrays?
Date: Mon, 13 Mar 2006 19:02:07 +0100
Message-ID: <dv4c2v$4se$1_at_nntp.fujitsu-siemens.com>
DA Morgan schrieb:
> Volker Hetzer wrote:
>> DA Morgan schrieb:
>>> Volker Hetzer wrote:
>>>> Hi!
>>>> I'm trying to use the oo4o driver with bind arrays for inserts.
>>>> Now, when inserting a large number of rows, like 1000005,
>>>> I create bind arrays for let's say, 10000 rows. Then I read the data
>>>> from somewhere else, putthem into the bind arrya and whenever I've got
>>>> 10000 rows, I insert. Which works fine, until I've done 1000000 rows.
>>>>
>>>> Then I read the last 5 rows and then what?
>>>> Oracle complains aither about duplicate values or NULLs, depending onm
>>>> wether the array was filled before.
>>>>
>>>> So, what do I do with the odd rows?
>>>>
>>>> Lots of Greetings!
>>>> Volker
>>>
>>> Insufficent information, beginning with the Oracle version number and
>>> lack of DML, to help you.
>> Client 10.2.0.1 WinXP,
>> DML: insert into testtable (testcolumn) (:testbindarray)
>>
>> Lots of Greetings!
>> Volker
> > Given the still insufficient information I will give what may well be > an insufficient answer. > > Use BULK COLLECT with the LIMIT clause to load the array. > Use FORALL wih array.FIRST .. array.LAST to do your inserts. > It can also handle sparse arrays. > > A demo of this can be found in Morgan's Library at www.psoug.org under > Bulk Binding.
[Quoted] Thank you for bothering. I admit I accidentally hid the oo40 stuff better than I intended. Sorry for having been flippant.
[Quoted] 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
Received on Mon Mar 13 2006 - 19:02:07 CET