Re: Create #TempTable in PL/SQL?
Date: 1996/08/07
Message-ID: <4uanvt$t4n_at_inet-nntp-gw-1.us.oracle.com>#1/1
In article <32088F9E.56D5_at_msn.com>, Roderick Prince <rprince_at_msn.com> writes:
|> Scott,
|> Thanks for the feedback.
|>
|> I will have to do something similar to your suggestion with compromises
|> having to be made be the calling application. The problem with Oracle
|> not having temp tables (like SQLServer) is that the original application
|> expects to bind into the results of a stored procedure call without
|> having to concern itself to much with the cleanup of the results (thus
|> PL/SQL tables are out I expect). I can solve the persistance problem via
|> "real" tables, but then have to manage not only the removal of the
|> "temporary" result sets but also need some method of identifying whose
|> results they are - all without completely re-writing the calling
|> application (QA team and proj. mgr would not be excited)
Another option is cursor variables. Cursor variables are similar to Sybase result sets, and are available starting with Oracle 7.2. You can return a variable for a particular query to a client program. The client program then fetches from the cursor variable, just like a fetch from a cursor. You can't insert into them like it sounds like you can with temporary tables, but it may be better then using persistent tables.
|>
|> Fortunately those who came before me put a class wrapper around all the
|> database calls - now I just have to hope that they fully encapsulated
|> the interface, can uniquely identify the current user, etc...
If you want to use persistent tables, add a column for the current user and/or current session ID, then query based on these. That way you can have one table, but multiple users using them. You would have to handle deleting from it, though.
|>
|> Thanks again,
|> Roderick...
Scott Urman Oracle Corporation surman_at_us.oracle.com
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2 Published by Oracle Press - http://www.osborne.com/oracle/index.htm
"The opinions expressed here are my own, and are not necessarily that of Oracle Corporation"
Received on Wed Aug 07 1996 - 00:00:00 CEST