TopBanana

I need to return a set of rows in a procedure, and then delete them in that same procedure. Is there a neat way of doing this without temp tables? Something like an in-memory cursor maybe?

tarmac terror

iTrader: (2)

Are you wanting to move data or are you working with queued data?
Please test this first - I haven't done much work in Oracle in years I think it could be as simple as this;

DELETE FROM <table> WHERE <conditions> RETURNING <column1>, <column2> INTO <array1>, <array2>

The hassle is that if you have 50 columns per record you have to have one array for each of them.

Would this also work using the cursor method that your suggested.

DECLARE CURSOR c_updates
IS
SELECT * FROM table1 t1
LEFT JOIN table2 t2 ON t1.field = t2.field
WHERE t2.field IS NULL
FOR UPDATE OF t1.field;
a_record c_updates%ROWTYPE;

BEGIN
OPEN c_updates;
LOOP
FETCH c_updates INTO a_record;
EXIT WHEN c_updates%NOTFOUND;
DELETE FROM table1
WHERE CURRENT OF c_updates;
END LOOP;
CLOSE c_updates;
END;

If I have just posted a pile of poo apologies - like I say, out of Oracle years ages ago, if indeed I was ever really into it.