Re: partially filled bind arrays?
From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 13 Mar 2006 08:24:09 -0800
Message-ID: <1142267030.289453_at_yasure.drizzle.com>
>> 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.
Date: Mon, 13 Mar 2006 08:24:09 -0800
Message-ID: <1142267030.289453_at_yasure.drizzle.com>
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.
-- Daniel A. Morgan http://www.psoug.org damorgan_at_x.washington.edu (replace x with u to respond)Received on Mon Mar 13 2006 - 17:24:09 CET