By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,473 Members | 3,292 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,473 IT Pros & Developers. It's quick & easy.

Trying to store field values in a variable

P: n/a
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.
Jul 19 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a

"RayP" <ra**********@ppa.mod.uk> wrote in message
news:52**************************@posting.google.c om...
| 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
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.