Re: Need SQL Server Temp Table equivalent (challenge!)

Hi Kin Ng, thanks for writing this:
> Ok guys. I think you all are still missing my points. Here are the
> problems with you way of "dynamic" approach:
>
> 1. You created a PERMANENT table even though you created using
> dynamic sql.
> 2. You can't SCALE because you can't have more than 1 person using
> it...unless you name the table differently every time.
> 3. Regardless, you have to delete it because it is permanent.
>
> I am trying to scale and that's why having a dynamic table that is
> visible to only one session, and each session's table may have
> different table structure are critical. Of course, the system should
> take care of the cleaning up. Being able to do this in a SP
>
> select * from myTempAndTruelyDynamicTable
>
> and return the result set is cool as well. But that's another topic.
>
> Guys, I apologize if I offended any of you. But I still can't see how
> you can do that easily in Oracle.
>
> Oracle's Temp Table solution is close but it still requires fixed
> columns. Just imagine if the Oracle's Temp table structure can be
> changed for each session and won't effect other sessions, then my
> problem is solved.
>

I'm not offended. Perhaps if you explain what business problem you're trying to solve, someone can help you. You are absolutely right ... Oracle's implementation of temporary tables is "lacking" a lot of features that other RDBMS vendors provide. But I think that's because you usually don't need temporary tables to solve a particular business problem in Oracle. There are other ways.

I can understand your desire to provide a technical solution that you've found useful in the past with other RDBMSs ... but there's probably a better (or at least, different) approach that's appropriate in Oracle.

Perhaps you simply need to use Oracle's memory "context" area, which is very much like a temporary table that can be dynamically implemented on a per user basis, with different info being collected and cleaned up when no longer used (like the heap in C). But without knowing something about what BUSINESS need you're trying to satisfy, I can't say for sure. So far, you've only asked how to implement a particular TECHNICAL need using Oracle.

-- 
[:%s/Karsten Farrell/Oracle DBA/g]