Temporary table dynamic SQL cursor

Folks,

I am fairly knew to the PL/SQL Oracle world. I face the following problem:
returning a cursor from a function or stored procedure AND dynamic SQL AND temp table.

I am using Oracle 8.0.4

This problem has been discussed here before and the solution suggested by couple of people is the following:

...

function foo(param_1, param_2) return ref cursor is

o drop the temp table is any exists
o create a new temp table (using dynamic SQL) as select bla bla bla based on the parameters passed to that function, the temp table created is different every time the function is invoked; o open a cursor and make it point to that temp table o return the cursor to that caller of our function

...

The problem is when foo function is invoked many time at a very high rate say every 3 Sec
the package foo is in becomes 'corrupted' and my temp table is gone (I must have one created all the time to allow the function/package to compile).
The diagnostic I draw from this is the foo function catches up on itself While the temp table from a previous invocation is being created another invocation of foo drop the table etc..

Now what I am looking for a way of safely returning 'dynamic' data to a function caller
at a very high rate, in a multi-threaded environment; so it could be

o a solution that does not require temp table at all, but still using cursor,
o have a slick way of having a separate temp table per user, per session per foo invocation
o any other ???

I would be greatful if any of you could help me on this

Cheers
JC

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Dec 14 1999 - 04:42:49 CST