Oracle updating cursor
In earlier versions of Oracle, this error was ignored which lead to occasionally incorrect results.
Once I have done this I need to use this status for calling custom business logic and not the original status retrieved by the cursor. Emp_No; IF(v_status = 'Active') THEN -- Custom Business Logic ELSE -- Business logic END IF; END LOOP; clause are released and other sessions are free to update the same rows.
In later versions of Oracle, you'll get an "ORA-01002: fetch out of sequence" if you do this.
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.
WHERE CURRENT OF & FOR UPDATE The WHERE CURRENT OF clause is used in some UPDATE and DELETE statements.The WHERE CURRENT OF clause in an UPDATE or DELETE statement states that the most recent row fetched from the table should be updated or deleted.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.
DECLARE this Student Student%ROWTYPE; CURSOR Maths_Student IS SELECT * FROM Student WHERE SID IN (SELECT SID FROM Take WHERE CID = ’CS145’) FOR UPDATE; BEGIN OPEN Maths_Student; LOOP FETCH Maths_Student INTO this Student; EXIT WHEN (Maths_Student%NOTFOUND); IF (this Student.Now I would want to update my status in Employee table from another table using the Emp_no. I declared a variable called v_status by the way FOR Rec IN Cur LOOP BEGIN UPDATE Employee SET status = (select a.status from Employee_Status where a. Emp_No) WHERE CURRENT OF Cur ; COMMIT; END; SELECT status INTO v_status FROM Employee where Emp_No = rec.