| unable to retrieve data from cursor [message #231568] |
Tue, 17 April 2007 09:54  |
deahayes3
Messages: 203
Registered: May 2006
|
Senior Member |
|
|
Really need help I have 3 cursors 1 is the outer/main cursor that is need to retrieve data from the other two cursors by debugging I see that the first cursor works, but when I get to the second one the data is not being fetched however if I take the same select statement that is in cursor 2 and 3 and put in SQL I retrieve my data but for some reason it will not retrieve in the cursor, please let me know if I am missing something
CURSOR GET_ITEM_CUR IS --cur 1
SELECT
PA.SEGMENT1 PROJECT_NUMBER,
PAT.TASK_NUMBER TASK_NUMBER,
PA.PROJECT_ID PROJECT_ID,
PAT.TASK_ID TASK_ID,
FROM
PA_TASKS PAT,
PROJECTS_ALL PA
WHERE PAT.PROJECT_ID = PA.PROJECT_ID
AND PA.SEGMENT1 = NVL(P_PROJECT_NUM,PA.SEGMENT1);
get_item_rec get_item_cur%rowtype;
--cur 2
CURSOR MPS_ITEM_CUR IS SELECT
MRP.PROJECT_ID,
MRP.PROJECT_NUMBER,
MRP.TASK_ID,
MRP.TASK_NUMBER,
MTL.SEGMENT1 ORG_ITEM
FROM MRP_SCHEDULE_V MRP, MTL_ITEMS MTL
WHERE MRP.PROJECT_ID = get_item_rec.PROJECT_ID AND MRP.TASK_ID = get_item_rec.TASK_ID AND mrp.INVENTORY_ITEM_ID = mtl.INVENTORY_ITEM_ID;
MPS_ITEM_REC MPS_ITEM_CUR%ROWTYPE;
FOR GET_ITEM_REC IN GET_ITEM_CUR
LOOP
v_project_number := get_item_rec.project_number;
v_task_number := get_item_rec.task_number;
--cur 2 \
-- NEEDS FIXING
OPEN MPS_ITEM_CUR;
FETCH MPS_ITEM_CUR INTO MPS_ITEM_REC;
IF MPS_ITEM_CUR%FOUND THEN
v_mps_item:= MPS_ITEM_REC.SEGMENTS;
v_schedule:= MPS_ITEM_REC.SCHEDULE;
v_schedule_date := MPS_ITEM_REC.SCHEDULE_DATE;
ELSE
NULL;
END IF;
CLOSE MPS_ITEM_CUR;
insert data into table
.....
end loop;
|
|
|
|
|
|
| Re: unable to retrieve data from cursor [message #232170 is a reply to message #231631] |
Thu, 19 April 2007 13:29  |
deahayes3
Messages: 203
Registered: May 2006
|
Senior Member |
|
|
|
what I did to get it to work was create local variable (v_project_id)and insert get_item_rec.PROJECT_ID into new variable (v_project_id) at the beginning of the loop and set the cursor MPS_ITEM_CUR to = v_project_id instead of get_item_rec.PROJECT_ID for some reason forms 9i doesnt like using value of one cursor into another but I did this before in forms 6i and it worked fine.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
