Re: SQL server temporary tables to oracle cursors

I do this all of the time. But create a permanent table and when you are done truncate it. As you will be reusing the table it makes no sense to repeatedly create it and drop it.

Usually I create a STAGING or QUARANTINE schema with its own tablespace just for this purpose. I don't like to mix dirty records with validated records.

But as I said ... you can do it using Native Dynamic SQL. I just strongly urge you not to as in Oracle there is no need to play those SQL Server-type games to get around the product's weaknesses.

Daniel Morgan

Jon Waterhouse wrote:

> 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.
>
> 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. 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.
>
> 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
> >
Received on Fri Mar 15 2002 - 15:49:12 CST