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.

"RayP" <rayproudfoot (AT) ppa (DOT) mod.uk> wrote


you cannot directly assign a column to a variable -- it is not allowed, and
does not make sense

the problem line:

is attempting to assign a scalar value from the specified column
(PWO_PWEDATE) of the specified table (PERSON_WEB_OTTOTALS) with no
indication of which row in the table -- which is what the error is trying to
communicate

you can only assign database values to variables via a SELECT statements --
SELECT INTO, or via a cursor (FETCH, for loop, etc.)

you need to get yourself a good PL/SQL 101 book or tutorial, or just read
the PL/SQL manual section on interacting with the database -- that covers
these concepts

++ mcs