Trying to store field values in a variable
Date: 26 Jul 2004 08:48:17 -0700
Message-ID: <52b901ef.0407260748.5ac8d61d_at_posting.google.com>
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. Received on Mon Jul 26 2004 - 17:48:17 CEST