Re: SQL server temporary tables to oracle cursors

In article <39206c8b.0203151003.4b23fa2b_at_posting.google.com>, jonwaterhouse_at_mail.gov.nf.ca says...
>
>Daniel,
>
>Thanks for your reply. Having learnt a bit more about how Oracle and
>PL/SQL works I still don't see a great solution (in a programming
>sense) to the problem. What I need is a table simlar to a permanent
>table I want to keep, but without all of the constraints, so that it
>can accept messier data than the real table would. Things are cleaned
>up, then transferred into the permanent table. At this point there is
>no need for the temporary table or any of the data it contains to
>remain in the database.

ahh, but there IS a need for the temporary table to remain in the ORACLE database. You point out why the need is there right below.

>
>From Oracle's point of view, this table needs to exist before a stored
>procedure using it can compile. What I don't like is that when the
>sensible step of cleaning up the garbage (dropping the temporary
>table) is taken, the package and procedures become a non-functional
>piece of junk.

that "sensible step" is only "sensible" in SQLServer. It is not "sensible" in Oracle.

Look at it this way - the temporary table structure is metadata, it is documentation about your process. It is part of your process. Your process requires a temporary table, a procedure, maybe some sequences, other stuff.

It makes no more sense to drop the temporary table as part of clean up (to me) then it would to drop the procedure (i mean hey -- your not actually running the procedure all of the time are you? so why have it cluttering up your database? drop it when you are not using it).

What makes sense depends on your perspective and how the tool works. Consider the person going the other way "what do you mean my temporary tables don't exist in the schema -- whats up with that? why do i have to create them every time". To them, the other way is "not sensible".

>Sure, you could put a comment at the top saying "To
>compile this package you first need to create this table and that
>table", but I would file that in the "insecure junk" school of
>programming.
>

And I would just leave the temporary table be there as that is the way it is implemented in Oracle. Oracle is not SQLServer (and vice versa). We are different, there will be major fundemental differences between the two... That is clean, that is sensible (at least in the Oracle world).

>So, still looking for a more elegant way of dealing with this.
>
>damorgan <dan.morgan_at_ci.seattle.wa.us> wrote in message
>news:<3C7BF7F3.3AFB789A_at_ci.seattle.wa.us>...
>> In Oracle you NEVER EVER create tables inside of stored procedures. It is not
>> that you can't but rather that there is no need to. Oracle is not just SQL
>> Server with another company's name on the CD. The architecture, transactions,
>> etc. are all very different.
>>
>>To work in Oracle you must learn how Oracle works ... and more importantly how
>> it does not work. Unless, of course, you wish to create slow, unscalable,
>> insecure junk.
>>
>>I strongly urge you to purchase Tom Kyte's book "expert one-on-one Oracle" and
>> read it. Especially the first three chapters.
>>
>> Daniel Morgan
>>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp