Re: Temporary table, DynamicSQL Cursor

A copy of this was sent to jeanch_at_my-deja.com (if that email address didn't require changing) On Wed, 15 Dec 1999 20:44:27 GMT, you wrote:

>
>> 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.

but the package will ALWAYS be invalid. the first person to run it makes it that way -- it will run once and then *never* again.

>...
>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

same problem. temporary tables in Oracle work just the same as permanent -- just the data is gone.

you would ONCE in a database issue:

create global temporary table foo ( x int ); .....

you would *not* dynamically create a table in a stored procedure (as you do in sybase for example). it does not work that way.

You might as well find another way right now as temporary tables will *not* change any of this.

You are trying to have a stored procedure return an arbritrary result set (arbitrary set of columns from an arbitrary table) -- that does not compute in PL/SQL. It just does not work that way.

>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

you need to use one table. the select list will remain constant (although in Oracle8i with dynamic ref cursors, eg: open X for plsql_variable; you could relax this and return only the columns they are truely interested in).

>Cheers
>JC
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Dec 16 1999 - 07:36:50 CST