Re: Hi all

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Tue, 18 Aug 2009 01:31:33 -0700
Message-ID: <7ett1sF2hqo0pU1_at_mid.individual.net>

Mark D Powell wrote:

> 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.
There is a lot of nonsense here. Sorry to to be so blunt. Actually I'm not sorry at all. ;-)

Unless you are running in transaction level read consistency the problem is EXACTLY the same as far as storage of temporary table is concerned. It's just that Oracle doesn't call them temporary tables they call them associative arrays (INDEX BY) (or some other collection objects of your choosing). And of course they are defined locally using procedure local TYPE declarations.
Pile BULK COLLECT and FOR ALL on top of that and it is quite clear that PL/SQL pays a lot of attention to local temporary tables. Just not under that name.

So that Ford Model T is not that far of from the Ferrari after all.

Now you have to pay attention with scoping. While in Sybase/SQL Server local temp tables are visible across routine levels in Oracle you would pass the associate array down as a parameter.

In summary: Read up on associative arrays. You will find the mapping straight forward.

Cheers
Serge

-- 
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

Received on Tue Aug 18 2009 - 03:31:33 CDT