Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!newsfeed.news2me.com!newsfeed.icl.net!newsfeed.fjserv.net!proxad.net!freenix!deine.net!fu-berlin.de!news.uar.net!carrier.kiev.ua!news.ukr.net!news.donbass.net!not-for-mail From: "Sergey Balter" Newsgroups: comp.databases.oracle.misc Subject: How to bypass FORALL in SELECT .. BULK COLLECT Date: Mon, 11 Nov 2002 14:37:56 +0200 Organization: Donbass Internet Center DIPT Lines: 55 Message-ID: NNTP-Posting-Host: nas3-user82.donbass.net X-Trace: dipt.donbass.net 1037018300 5990 195.184.195.82 (11 Nov 2002 12:38:20 GMT) X-Complaints-To: usenet@dipt.donbass.net NNTP-Posting-Date: 11 Nov 2002 12:38:20 GMT X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.00.2919.6600 X-MIMEOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 Xref: newsfeed1.easynews.com comp.databases.oracle.misc:89198 X-Received-Date: Mon, 11 Nov 2002 05:38:06 MST (news.easynews.com) 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