Re: Hi all
Date: Mon, 17 Aug 2009 09:06:42 -0700 (PDT)
Message-ID: <e584ba53-2de5-4386-a158-15ca4cb0e6fa_at_k30g2000yqf.googlegroups.com>
On Aug 17, 6:35 am, sybrandb <sybra..._at_gmail.com> wrote:
> On 17 aug, 12:24, pal <jayadevpal..._at_gmail.com> wrote:
>
> > Can any one tell me how to declare a temporary table in a trigger(like
> > in SQL Server)?
>
> 1 In Oracle you don't NEED temporary tables. Oracle is not SQL-server
> and Oracle has a different read-consistency model.
> Due to the read-consistency model -or better: lack thereof- SQLserver
> requires temporary tables. But then Sqlserver is to Oracle what a T-
> Ford is to a Ferrari.
> You shouldn't try to turn a Ferrari in a T-Ford.
> 2 Creating database objects on the fly must be considered pure EVIL
> due to side effects and lack of control on the database. One of the
> side effects is DDL is automatically committed
> 3 Moreover, due to side-effects COMMIT is not allowed in a trigger.
>
> You are recommended to learn Oracle, or to keep driving your T-Ford.
> At least Oracle is available in different colors, and Sqlserver isn't.
>
> ---
> Sybrand Bakker
> Senior Oracle DBA
In additon to what Sybrand said, in Oracle you define objects once and use them many times. This is true with global temporary tables also. You define it once then you just reference it. Every user gets a private copy with the first insert to populate the temporary table.
Oracle is built on a different underlying architecture from SQL Server which as based on Sybase way back when. Before you try to code anything in Oracle you need to read the Oracle Application Developers Guide. Following that with the Concepts Guide would be wise.
If you just try to port straight SQL Server code into Oracle you are likely to have a lot of trouble.
Oracle provides a free utility, Oracle Migration Workbench, that will convert SQL Server code to Oracle code with some limitations. You can find the utility at http://otn.oracle.com
HTH -- Mark D Powell -- Received on Mon Aug 17 2009 - 11:06:42 CDT