I'd appreciate some help I'm having trying to run a cursor. First,
some background.
The Status field of all records on Table A needs changing from 1 to 0
where there is no corresponding record on Table B. For each record
that is changed the PWE, Staff Number and Status needs to be output to
the screen. I have successfully run the SELECT statement but can't
output anything to the screen.
I've tried outputting the value of the table field directly and when
that didn't work I tried storing it in a variable and then outputting
the variable. That didn't work either and even the action of storing
the value of the field in a variable generates an error - 'PLS-00357:
Table,View Or Sequence reference 'PERSON_WEB_OTTOTALS.PWO_PWEDATE' not
allowed in this context'.
Here's the code as it currently stands...
SET SERVEROUTPUT ON
DECLARE
V_PWE VARCHAR2(10);
CURSOR UOT IS
SELECT OT.PWO_PWEDATE, OT.PWO_STAFF_NUMBER, OT.PWO_STATUS
FROM PERSON_WEB_OTTOTALS OT, PERSON_OVERTIME PO
WHERE OT.PWO_PWEDATE = PO.PO_WEEK_END_DATE(+)
AND OT.PWO_STAFF_NUMBER = PO.PO_PERSON_ID(+)
AND OT.PWO_CUST_ID = PO.PO_CUST_ID(+)
AND PO.PO_WEEK_END_DATE IS NULL
AND OT.PWO_STATUS = 1
ORDER BY OT.PWO_PWEDATE,OT.PWO_STAFF_NUMBER
FOR UPDATE OF OT.PWO_STATUS;
BEGIN
FOR UOT_RECORD IN UOT LOOP
UPDATE PERSON_WEB_OTTOTALS
SET PWO_STATUS=0
WHERE CURRENT OF UOT;
V_PWE:=PERSON_WEB_OTTOTALS.PWO_PWEDATE; -- problem line --
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLCODE||SQLERRM);
END;
/
I'm totally baffled with all this. I'm very much a beginner with
cursors and any help that someone can give me would be greatly
appreciated - thanks.