No more WHERE CURRENT OF

Question:
I have a question for you regarding BULK COLLECT. I have a program that defines a CURSOR using FOR UPDATE OF col1. I then run a cursor FOR loop against that cursor and perform an UPDATE against the table using the WHERE CURRENT OF syntax.
I would like to switch to a BULK COLLECT to query the data from my cursor. Can I still use FOR CURRENT OF as I perform an update for each row fetched by that cursor?
Answer:
Akanksha, developers use CURSOR ... FOR UPDATE OF when they need to proactively lock all rows being queried, usually because they intend to change the data in those rows shortly after querying them. They can the use the CURRENT OF clause in a DELETE or UPDATE statement to easily refer to the latest row fetched by the cursor; they do not, in other words, have to write a WHERE clause to identify the row of interest. CURRENT OF is handy both from the standpoint of readability and efficiency.
Here is brief example showing the application of this syntax:
1. I create a relational table and populate it with data.
CREATE TABLE tbl (pk INTEGER PRIMARY KEY, n NUMBER)
/

BEGIN
FOR j IN 1 .. 10
LOOP
INSERT INTO tbl VALUES (j, j);
END LOOP;
END;
/
2. I declare an explicit cursor with a FOR UPDATE OF clause and then within a cursor FOR loop, use CURRENT OF to update the latest row fetched.
DECLARE
CURSOR cur
IS
SELECT n
FROM tbl
WHERE pk > 2
FOR UPDATE OF n;
BEGIN
FOR j IN cur
LOOP
UPDATE tbl
SET n = n * 2
WHERE CURRENT OF cur;
END LOOP;
END;
/
3. I declare an explicit cursor with a FOR UPDATE OF clause and then within a simple loop, use CURRENT OF to update the latest row fetched. So a cursor FOR loop is not required to take advantage of CURRENT OF.
DECLARE
CURSOR cur
IS
SELECT *
FROM tbl
WHERE pk > 2
FOR UPDATE OF n;

rec tbl%ROWTYPE;
BEGIN
OPEN cur;

LOOP
FETCH cur
INTO rec;

EXIT WHEN cur%NOTFOUND;

UPDATE tbl
SET n = n * 2
WHERE CURRENT OF cur;
END LOOP;
END;
/
4. Finally, I try to use a cursor variable in the CURRENT OF clause, but Oracle raises this exception:
PLS-00413: identifier in CURRENT OF clause is not a cursor name

DECLARE
cur sys_refcursor;
rec tbl%ROWTYPE;
BEGIN
OPEN cur FOR
SELECT n
FROM tbl
WHERE pk > 2
FOR UPDATE OF n;

LOOP
FETCH cur
INTO rec;

EXIT WHEN cur%NOTFOUND;

UPDATE tbl
SET n = n * 2
WHERE CURRENT OF cur;
END LOOP;
END;
/
We can conclude from these examples that the CURRENT OF construct is intimitely tied to PL/SQL's cursor processing within a loop, but it is not a part of the SQL language itself.
If you switch to using BULK COLLECT in order to speed up your query processing (a truly excellent idea), you are foregoing the use of the cursor loop processing. You then also lose the ability to apply WHERE CURRENT OF to a DML statement that works with a row from the collection populated by the BULK COLLECT statement.
Let's take a look at an example. The code below gives a $100 raise to each person in department 10. (Note: we don't really need BULK COLLECT or even PL/SQL to accomplish this; it is intended merely to offer a simple example of the syntax.)
I declare an explicit cursor and then use FETCH BULK COLLECT to retrieve up to 100 rows with each context switch. I then iterate through the elements in the collection and perform my update. Notice that I have included a WHERE CURRENT OF clause on my update. This code compiles without error, but when I execute the block, I get the ORA-01410: invalid ROWID error. WHERE CURRENT OF tries to refer to the "latest" row fetched by the cursor, but it doesn't have a valid ROWID with which to work.
CREATE TABLE otn_employee (
employee_id NUMBER PRIMARY KEY,
department_id NUMBER,
salary NUMBER)
/

BEGIN
INSERT INTO otn_employee
VALUES (1, 10, 1000);

INSERT INTO otn_employee
VALUES (2, 20, 900);

INSERT INTO otn_employee
VALUES (3, 10, 750);

INSERT INTO otn_employee
VALUES (4, 20, 800);

INSERT INTO otn_employee
VALUES (5, 30, 1500);

COMMIT;
END;
/

DECLARE
CURSOR dept10_cur
IS
SELECT *
FROM otn_employee
WHERE department_id = 10
FOR UPDATE OF salary;

-- Declaration of associative array of records based on otn_employee
TYPE otn_employee_aat IS TABLE OF otn_employee%ROWTYPE
INDEX BY BINARY_INTEGER;

l_otn_employee otn_employee_aat;
l_row PLS_INTEGER;
BEGIN
OPEN dept10_cur;

LOOP
FETCH dept10_cur
BULK COLLECT INTO l_otn_employee LIMIT 100;

EXIT WHEN l_otn_employee.COUNT = 0;
l_row := l_otn_employee.FIRST;

WHILE (l_row IS NOT NULL)
LOOP
UPDATE otn_employee
SET salary = l_otn_employee (l_row).salary + 100
WHERE CURRENT OF dept10_cur;

l_row := l_otn_employee.NEXT (l_row);
END LOOP;
END LOOP;

CLOSE dept10_cur;
END;
/
While WHERE CURRENT OF is no longer an option for you if you fetch with BULK COLLECT, I wouldn't be too upset about it. Instead, I suggest that you complete the transformation of your code from row-by-row processing to collection-based processing, rendering WHERE CURRENT OF irrelevant. Specifically, if you are executing DML statements within a loop as you indicate in your question, you really should switch over to using the FORALL statement to execute those updates or deletes much more efficiently.
Here is an example of the kind of code you can write to accomplish this; notice that I now declare a separate collection for employee ID and salary. Since I cannot reference fields of records in a FORALL statement, I must BULK COLLECT into collections of scalars.
DECLARE
CURSOR dept10_cur
IS
SELECT employee_id, salary
FROM employee
WHERE department_id = 10
FOR UPDATE OF salary;

TYPE employee_id_aat IS TABLE OF qnxo_employee.employee_id%TYPE
INDEX BY BINARY_INTEGER;

l_employee_id employee_id_aat;

TYPE salary_aat IS TABLE OF qnxo_employee.salary%TYPE
INDEX BY BINARY_INTEGER;

l_salary salary_aat;
l_row PLS_INTEGER;
BEGIN
OPEN dept10_cur;

LOOP
FETCH dept10_cur
BULK COLLECT INTO l_employee_id, l_salary LIMIT 100;

EXIT WHEN l_employee_id.COUNT = 0;

FORALL l_index IN l_employee_id.FIRST .. l_employee_id.LAST
UPDATE employee
SET salary = l_salary (l_index) + 100
WHERE employee_id = l_employee_id (l_index);

l_row := l_employee_id.NEXT (l_row);
END LOOP;

CLOSE dept10_cur;
END;
/