speed dating ambassador hotel cork - Updating record from cursor oracle

CREATE TABLE employee 2 (employee_id NUMBER(7), 3 last_name VARCHAR2(25), 4 first_name VARCHAR2(25), 5 userid VARCHAR2(8), 6 start_date DATE, 7 comments VARCHAR2(255), 8 manager_id NUMBER(7), 9 title VARCHAR2(25), 10 department_id NUMBER(7), 11 salary NUMBER(11, 2), 12 commission_pct NUMBER(4, 2) 13 ); Table created.

updating record from cursor oracle-44

Once we open a cursor having a FOR UPDATE clause, all the rows returned by the SELECT statement are locked for our changes until a commit or a rollback is placed to release the lock.

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.

Inside a cursor loop, WHERE CURRENT OF allows the current row to be directly updated.

When the session opens a cursor with the FOR UPDATE clause, all rows in the return set will hold row-level exclusive locks.

Other sessions can only query the rows, but they cannot update, delete, or select with FOR UPDATE.

Oracle provides the FOR UPDATE clause in SQL syntax to allow the developer to lock a set of Oracle rows for the duration of a transaction.

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.

Rows Only does not seem to work with the FOR UPDATE clause.

Tags: , ,