SELECT FROM TABLE in PL/SQL procedure

From: Sergey Balter <balter_at_kompas.donetsk.ua>
Date: Thu, 6 Feb 2003 12:08:26 +0200
Message-ID: <b1tbvr$2nhn$1@dipt.donbass.net>

Hi,

I've found the following bad situation
Using Oracle 8.1.7.0. (Windows)

When querying data from PL/SQL procedure (really from package)

SELECT column_name1, column_name2, ...
BULK COLLECT INTO .....
FROM TABLE(CAST(ALots AS TLot_Set))

a session SOMETIMES stalls. It happens not very often, but unpredictable..

When using SELECT * instead of column names it seems to be all ok

But I need to calculate some sums with GROUP BY, i. e.

SELECT Sum(Quantity1) ... GROUP BY ProductId

I've tried to bypass this by ising following query:

SELECT Sum(Quantity1) FROM
SELECT * FROM TABLE(CAST(ALots AS TLot_Set)) ) GROUP BY ProductId

But the problem stay the same (session stalling).

Now I forced to calculate sums and perform grouping
manually :-((((

QUESTIONS:
1) Can Serivce Pack 8.1.7.4 to solve the problem? 2) Is there a way to change a query o something else?

Regards,

Sergey Balter Received on Thu Feb 06 2003 - 04:08:26 CST