While updating records using cursor
In the below example, the cursor CUR is associated with a SELECT statement having the tables EMPLOYEES and joined in its FROM clause. EMPLOYEE_ID clause in the below snippet locks up only rows of the EMPLOYEES table, thus making the DEPARTMENTS table available for modification to the other session users. For this, the cursor CUR is associated with a SELECT statement joining the EMPLOYEES and the DEPARTMENTS tables with a FOR UPDATE OF clause on the employee ID.When we try to update the salary column of the EMPLOYEES table using the WHERE CURRENT OF clause, the block fails with an ORA-01410: invalid ROWID error as there is no way to specify the This scenario can be manhandled by fetching the rowid of the intended table in the cursor associated SELECT statement and using it in the WHERE clause of the DELETE or the UPDATE statement instead of the WHERE CURRENT OF clause as shown in the below listing. For complete notes on programming in PL/SQL, we recommend the book Advanced PL/SQL: The Definitive Reference by Boobal Ganesan.Or you might want to process some rows differently than others, and so need more than a simple loop.Because explicit cursors are so flexible, you can choose from different notations depending on your needs.In this scenario, the cursor result set can be limited using the traditional When we associate a SELECT statement with more than one table joined together to a cursor with a FOR UPDATE clause, we end up locking all the tables in the FROM clause of the SELECT statement, where we just need to lock a single table for our purpose.
Depending on the complexity of the processing that you want to do on the query results, you can use various notations.PL/SQL also supports dynamic SQL, which enables you to execute SQL data definition, data control, and session control statements dynamically.In addition, PL/SQL conforms to the current ANSI/ISO SQL standard.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.Doing other operations, such as procedure calls, might change the value of DECLARE emp_rec emp%ROWTYPE; BEGIN emp_:= 1500; emp_rec.ename := 'Steven Hill'; emp_:= '40000'; -- A %ROWTYPE value can fill in all the row fields.