473,324 Members | 2,356 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Trying to store field values in a variable

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
1 9446

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: James Turner | last post by:
I am trying to store formatted text (windows format) into a MySQL database and then retrieve it. The field in the database is a varchar. I cut and paste the test into a form field formatted, then...
4
by: Christopher Brandsdal | last post by:
Hi! I have a delicatg problem.... I have made a registration form for adding my friends information in a database. The problem is that I want to connect the persons with companies in the same...
8
by: mark | last post by:
Access2000 How do I write a query that combines the CTC field from each record below into one record? I need to concatenate the CTC field with a separator, like below: ...
4
by: Dave | last post by:
I used the following class and .aspx code below to understand how static works on variables and methods taken from...
3
by: Solution Seeker | last post by:
I want to Store the String value with Single Quotes in the Field of Database where if i try to Store the String value with Single Quotes (as it is) then it is throwing the error as SQL String...
12
by: dbuchanan | last post by:
I am designing a table to contain a field to stores a value that must indicate none, one, or many choices. The form will have a group box with checkboxes (multi-choice) You see the records in...
2
by: bonnie.tangyn | last post by:
Hello all Would it be possible to store javascript document.forms.value to ASP session as global variable? If it is not possible, how can I pass the javascript document.forms.value to...
5
by: Alan T | last post by:
Currently our SQL Server 2000 database table field is using char to store as boolean, ie. "T" or "F". Now we change the field from char to bit field. I am not sure how it has impact on the C#...
0
by: ramuygl | last post by:
want to create store procedure that. want to send the table name as argument and retrive the data of that argument. and want to store data in temperary table using the insert query. HERE I AM...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.