Home » Developer & Programmer » Forms » unable to retrieve data from cursor
unable to retrieve data from cursor [message #231568] Tue, 17 April 2007 09:54 Go to next message
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 #231631 is a reply to message #231568] Tue, 17 April 2007 15:09 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What happens if you INSERT data before CLOSING the cursor? Such as
insert data into table;

CLOSE MPS_ITEM_CUR;

Re: unable to retrieve data from cursor [message #232170 is a reply to message #231631] Thu, 19 April 2007 13:29 Go to previous message
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.
Previous Topic: How to jump to the parent record in a hierachical table in forms
Next Topic: can not retrieve data
Goto Forum:
  


Current Time: Fri Dec 02 14:28:51 CST 2016

Total time taken to generate the page: 0.08724 seconds