Re: Temporary table, DynamicSQL Cursor

> THAT is exactly what I mean. the drop invalidates anything that
references the
> dropped object. The procedure that has "open some_cursor for select
* from T"
> is invalidated by the dynamic drop. it must be recompiled before it
can be
> executed.
>
> During the execute - the procedure went INVALID, it needs to be
recompiled. If
> someone else executes it while this is executing -- it will
autorecompile and
> Bamm -- the error.
>
> Also, if you drop the table that someone has an open cursor on --
that'll do bad
> things as well.
>
> Bottom line -- don't do the drop / create. We must use one
consistent table for
> everyone.

Now I got you.
Right for us doing without temp table means a maintenance nightmare because we need to have a pre-defined (hardwired) set of temp table that our user might be interested in; Oops too bad; What I suggest instead is for the caller to check ...
if the package is invalid then it will have to try later if not then proceed.
...
I know it's not the best solution but it will keep us going until we have HP-UX11 (Oracle8i only available on UX11); couple of month down the line and then use proper temp tables The remaining problem though is if someone has a open cursor !!! I'll ask client to close the cursor before trying to invoke the package;

What's your thoughts on this
Cheers
JC

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Dec 15 1999 - 14:44:27 CST