Temp Table problem Oracle 8.0.0.16 1

[hmckillop]

How do I do the equivalent of a temp table in Oracle.
I seen CREATE GLOBAL TEMPORARY TABLE but this isnt available in the version I have.
Is there a replacement to this..

Here is the problem i have.
I have a many to many relationship TableA - TableC, so I created a link table TableB to handle this.
I need to remove all rows based on a value in tableA, problem being you need to delete TableB rows first due to ref. integrity.
I need to store the rows (and more specifically the foreign key field to table C) in temp Table so I can then delete all rows in table C.

How do people do this in Oracle at the minute?

Thanks

"I'm living so far beyond my income that we may almost be said to be living apart

 

[SantaMufasa]

H,

Why not just do the following:

CREATE table [b]Hold_Deletes[/b] as
SELECT * from [b]TableB[/b]
 WHERE <TableA-based condition>;

DELETE from TableB
 WHERE <TableB columns match Hold_Deletes's columns>;

DELETE from TableC
 WHERE <TableC column matches Hold_Deletes's TableC column>:

DELETE from TableA
 WHERE <TableA column matches Hold_Deletes's TableA column>;

DROP table Hold_Deletes;

In the above code, the thing that makes the Hold_Deletes table temporary is that it doesn't survive long after your DELETE statements.

Let us know if this satisfies your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[hmckillop]

Thanks for your response.

I ended up using a cursor to loop through and delete the rows.
i.e.

create cursor on tableb
Delete same rows on tableb
loop through cursor
delete rwws on tablec
end loop
delete rows on tablea.

A star for effort!

"I'm living so far beyond my income that we may almost be said to be living apart