Using FORALL with associative arrays

From: Tor Hovland <teh_at_nospam.com>
Date: Wed, 16 Nov 2005 10:18:38 +0100
Message-ID: <LeudnRE1lYk2ZOfe4p2dnA@telenor.com>

I have the following procedure:

   TYPE testarray is table of int index by binary_integer;

   PROCEDURE testfast(par1 in testarray) is    begin

     FORALL i IN par1.FIRST..par1.LAST
       insert into dummy (test) values (par1(i));

   end;

This works, but is not a good solution with giant arrays, as the forall puts all of it in memory. The obvious solution would be to use BULK COLLECT and a LIMIT, but that only works with SQL types, and not with PL/SQL collections, as far as I can tell.

Somewhat oddly, if I call the insert statement directly from my client using array binding, the perfomance is as good as the procedure and it scales well with large arrays.

I would think it should be possible to achieve the same performance and scalability via a procedure, right?

I have to use an associative array, because that's the only collection type currently supported by ODP.NET, unless I'm mistaken.

-- 
Tor H.

Received on Wed Nov 16 2005 - 03:18:38 CST