| Using global temp tables [message #37503] |
Tue, 12 February 2002 08:25  |
LI810
Messages: 25
Registered: February 2002
|
Junior Member |
|
|
I am trying to create the stored procedure that is using 2 temporary tables.
I created the tables outside the stored procedure. When I populate one table the second table is truncated and the opposite.
Could you please explain why it is happened and how I can avoid it.
I came from the world of Sybase , where temp table are very useful and bring the accumption that Oracle can handle it also.
Thank you
|
|
|
|
| Re: Using global temp tables [message #37504 is a reply to message #37503] |
Tue, 12 February 2002 10:31  |
drewmoo
Messages: 6
Registered: February 2002
|
Junior Member |
|
|
did you create them like this ?
create global temporary table crud (stuff varchar2(2) ) on commit preserve rows;
you should also do a commit after inserting the rows into the temp table.
no idea why inserting into one would cause the other to truncate. i came from an Informix background and have to say that Oracle's methods for use of temporary tables are cryptic ... there really is no such thing as temporary tables ... they remain in the database until you explicitly truncate and drop them.
|
|
|
|
| Re: Using global temp tables [message #37523 is a reply to message #37503] |
Wed, 13 February 2002 00:31 |
pratap kumar tripathy
Messages: 660
Registered: January 2002
|
Senior Member |
|
|
hi,
i could not reproduce the problem .can u give a simple test case..table structure and procedure. i tried, but it is not truncating other table.
here is how i tested
create global temporary table xyz_1 (a varchar2(5) ) on commit preserve rows
/
create global temporary table xyz_2 (a varchar2(5) ) on commit preserve rows
/
create or replace procedure xyz_test as
begin
insert into xyz_1 values('aa');
end;
/
cheers
pratap
|
|
|
|
| Re: Using global temp tables [message #38763 is a reply to message #37503] |
Mon, 13 May 2002 10:29 |
Adi
Messages: 38
Registered: May 2002
|
Member |
|
|
|
In most cases a 'exists' or decode command can do the job of temp table but developers will use temp tables to get a kick out of it !!. In SQL or sysbase use the case statement, code may look messy but .. atleast its efficient !!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
