Re: partially filled bind arrays?
From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 13 Mar 2006 12:17:47 -0800
Message-ID: <1142281049.26800_at_yasure.drizzle.com>
> Thank you for bothering. I admit I accidentally hid the oo40 stuff
> better than I intended. Sorry for having been flippant.
>
> 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
Date: Mon, 13 Mar 2006 12:17:47 -0800
Message-ID: <1142281049.26800_at_yasure.drizzle.com>
Volker Hetzer wrote:
> 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.
> Thank you for bothering. I admit I accidentally hid the oo40 stuff
> better than I intended. Sorry for having been flippant.
>
> 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
HTH
-- Daniel A. Morgan http://www.psoug.org damorgan_at_x.washington.edu (replace x with u to respond)Received on Mon Mar 13 2006 - 21:17:47 CET