This clause can also be extended to lock only the intended table in the SELECT query containing more than one table in joins using the FOR UPDATE OF clause.This clause becomes meaningless if we use a single table SELECT clause as the minimum Oracle has provided us with the WHERE CURRENT OF clause for both DELETE and UPDATE statements inside a cursor’s range to make changes to the last fetched row(s) from the cursor with an ease.By extending SQL, PL/SQL offers a unique combination of power and ease of use.You can manipulate Oracle data flexibly and safely because PL/SQL fully supports all SQL data manipulation statements (except ), transaction control statements, functions, pseudocolumns, and operators.Thus, any TCL operation on the cursor record set has to be done only after fetching all the rows from the cursor context area using a loop process similar to the above listing example.The row limiting clause introduced in the Oracle version 12c, Fetch First ..I'll re-run the pf31 block again as an AUTONOMOUS (sub or nested transaction -- runs AS IF it were in another session) transaction to show that it would be blocked. WORLD declare 2 PRAGMA AUTONOMOUS_TRANSACTION; 3 cursor c1 is 4 select PRS_WOO_PRCS_ID,prs_sts 5 from prcs_sts 6 where PRS_WOO_PRCS_ID = 'PF32' 7 for update of prs_sts; 8 begin 9 for c1_rec in c1 loop 10 update prcs_sts 11 set prs_sts = 'Y' 12 where current of c1; 13 end loop; 14 COMMIT; 15 end; 16 / PL/SQL procedure successfully completed. The only difference I can see in your plsql and mine is the kind of data that was selected for update.

After a TCL operation is performed, the cursor pointer gets reset and the cursor will be no longer accessible, thus results in an error when fetched further as shown below.Rows Only does not seem to work with the FOR UPDATE clause.When the Fetch clause is used with a cursor having a FOR UPDATE clause, the PL/SQL unit fails when we try to open the cursor with an ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc. The error description shows that the internal mechanism for the Fetch clause uses either a DISTINCT or a GROUP BY clause which is not permitted alongside the FOR UPDATE clause.At the end of a transaction that makes database changes, Oracle makes all the changes permanent or undoes them all.If your program fails in the middle of a transaction, Oracle detects the error and rolls back the transaction, restoring the database to its former state.