From: mcstockX@Xenquery   
      
   "RayP"  wrote in message   
   news:52b901ef.0407260748.5ac8d61d@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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)