EXPLAIN EXTENDED

From Stack Overflow:

I need to return a rowset from an Oracle 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?

Oracle has a nice feature of returning cursor variables from a stored procedure or an anonymous block.

As any cursor, the returned cursor is a handle to a stable rowset.

This means that it's not affected by any changes to database that were made after the cursor was open.

We can exploit this feature to achieve the required behavior.

Let's create a sample table:

CREATE TABLE t_deleter (id INT NOT NULL PRIMARY KEY, value VARCHAR2(50))
/
INSERT
INTO    t_deleter (id, value)
SELECT  level, 'Value ' || level
FROM    dual
CONNECT BY
level <= 10000
/
COMMIT
/

Now, we need to create a procedure that opens a cursor which selects some values and then deletes these values.

I'll make it an anonymous block for the sake of simplicity.

SELECT  COUNT(*)
FROM    t_deleter
/
VAR cur REFCURSOR
BEGIN
        OPEN    :cur FOR
        SELECT  *
        FROM    t_deleter
        WHERE   id <= 10;
        DELETE
        FROM    t_deleter
        WHERE   id <= 10;
END;
/
COMMIT
/
SELECT  COUNT(*)
FROM    t_deleter
/
PRINT cur

The script above, being run in SQL*Plus, will return the following:

  COUNT(*)
----------
     10000

PL/SQL procedure successfully completed.

Commit complete.

  COUNT(*)
----------
      9990


        ID VALUE
---------- --------------------------------------------------
         1 Value 1
         2 Value 2
         3 Value 3
         4 Value 4
         5 Value 5
         6 Value 6
         7 Value 7
         8 Value 8
         9 Value 9
        10 Value 10

10 rows selected.

What happened here?

First, we selected the number of values from t_deleter to make sure everything's in place:

SELECT  COUNT(*)
FROM    t_deleter
/

  COUNT(*)
----------
     10000

Second, we declared a REFCURSOR variable, and then ran the anonymous block than selected first ten records into this REFCURSOR variable.

Then we deleted these records and committed the transaction:

VAR cur REFCURSOR
BEGIN
OPEN    :cur FOR
SELECT  *
FROM    t_deleter
WHERE   id <= 10;
        DELETE
        FROM    t_deleter
        WHERE   id <= 10;
END;
/
COMMIT
/

PL/SQL procedure successfully completed.

Commit complete.

The transaction is committed. The deleted records don't exists anywhere but in the cursor.

Now, we make sure that the records are deleted:

SELECT  COUNT(*)
FROM    t_deleter
/

  COUNT(*)
----------
      9990

, and, finally, select the dead records from the cursor:

PRINT cur

        ID VALUE
---------- --------------------------------------------------
         1 Value 1
         2 Value 2
         3 Value 3
         4 Value 4
         5 Value 5
         6 Value 6
         7 Value 7
         8 Value 8
         9 Value 9
        10 Value 10

10 rows selected.

Here they are, the dead records.

Note that after the cursor is open and fetched, the records are dead forever, they can only be brought back by using Log Mining or restoring from the backup. The cursor cannot be reopen.