Oracle updating cursor dating twentieth century

07 Jul

SQL select * from Employee 2 / ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION ---- ---------- ---------- --------- --------- ---------- ---------- --------------- 01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected.SQL DECLARE 2 CURSOR employee_cur IS 3 SELECT * FROM employee 4 FOR UPDATE OF salary; 5 BEGIN 6 FOR employee_rec IN employee_cur 7 LOOP 8 UPDATE employee 9 SET salary = 10000 10 WHERE CURRENT OF employee_cur; 11 END LOOP; 12 END; 13 / PL/SQL procedure successfully completed.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.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.

oracle updating cursor-71oracle updating cursor-23oracle updating cursor-89

statement, but I don't see how that can fit into my current code. I want to allow a calling program to update a row in the cursor (I want to update the race location) returned by the query in my current code.

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.

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.

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.