Re: Trying to store field values in a variable

"RayP" <rayproudfoot_at_ppa.mod.uk> wrote in message news: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.

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

the problem line:

| V_PWE:=PERSON_WEB_OTTOTALS.PWO_PWEDATE; -- 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 Received on Mon Jul 26 2004 - 18:02:55 CEST