Create temp table by a unique name -- including the session-id (SID) for
example, or perhaps a sequence (might even include code to reset sequence
back to 1 when approaching max) -- lots of possibilities.
jeanch_at_my-deja.com wrote in message <8356v9$qus$1_at_nnrp1.deja.com>...
>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 - 21:59:52 CST
