How to bypass FORALL in SELECT .. BULK COLLECT

From: Sergey Balter <balter_at_kompas.donetsk.ua>
Date: Mon, 11 Nov 2002 14:37:56 +0200
Message-ID: <aqo8bs$5r6$1@dipt.donbass.net>

Using Oracle 8.1.6

I developed a stored procedure LOT

TYPE TIds IS Table of Integer;
TYPE TFloats IS Table of Float;

procedure LOT(Ids TIds) ....
is
Prices TFloats;
Quantities TFloats;
...
begin
...
for I in 1..Ids.Count loop

   ...
  SELECT ...
  BULK COLLECT INTO Prices, Quantities, ...   WHERE ... = Ids(I)
  .....
end loop;

end;

For each Id from IDS collection my procedure SELECTs several rows (avg. 2 or 3 rows). IDS contains about 1000 - 10000 items.

Productivity is very low.

I'm sure the battleneck here is in a numerous context switches between SQL engine and PL/SQL engine.

The point is to collect all data by single 'huge' SELECT instead of numerous 'short' SELECTs.

I. e. I need somethng like

FORALL I in 1..Ids.Count
SELECT ...
BULK COLLECT INTO Prices, Quantities, ... WHERE ... = Ids(I)

I know it's invalid syntax because PL/SQL doesn't support BULK COLLECT clause for SELECT in FORALL loop.

How should I bypass the problem?
Is a proper way to use temporary table as storehouse for IDS collection? Something else?

Using Oracle 8.1.6

Regards,
Sergey Balter Received on Mon Nov 11 2002 - 06:37:56 CST