loop in oracle not fetching all records, 1st record is fetched.

anumoses
anumoses used Ask the Experts™
on
Cursor C1 is
select e.mail_last_name||','||e.first_name as Name,
                     d.area_cd,
           d.division_cd||'  '||e.department_id as div_dept,
           e.employee_id,
                     e.social_security_no,
           e.position_cd,
           p.position_name,
         --e.hours_available
           ds.start_time,
           ds.start_hours||' '||start_minutes as start_tm,
           ds.end_time
  from department d,employee e,dept_staff ds, position p
 where d.department_id = e.department_id
    and d.division_cd = case when :hdr.division_cd = 'A' then d.division_cd else :hdr.division_cd end
   and e.position_cd = p.position_cd(+)
   and e.social_security_no = ds.social_security_no
   and ds.schedule_date = '25-JUN-2011'
   and ds.site_code = 'CS17';

Begin

Open C1;
first_record;
loop
Fetch C1 into
:dtl.emp_name,
:dtl.area_cd,
:dtl.div_dept,
:dtl.emp_id,
:dtl.social_security_no,
:dtl.position_cd,
:dtl.position_name,
:dtl.start,
:dtl.start_time,
:dtl.end_time;
--Exit when C1%notfound;
exit when :SYSTEM.LAST_RECORD = 'TRUE';
next_record;
END LOOP;
Close C1;

End;
----------------expected results

NAME|DIV_DEPT|POSITION_CD|POSITION_NAME

Baines,Nichole|W  5|PHMB|Mobile Phleboto
Bekoe,Stephen|W  5|PHMB|Mobile Phleboto
Guitierrez,Paige|W  5|PHTR|Phleb Training
Johnson,Teressa|W  5|SRPM|Sr Phlb - Mbl
King,Sonja|W  5|TLMB|Mobile Teamlead
Lemke,Mandy|W  5|PHMB|Mobile Phleboto
White,Katrina|W  5|PHMB|Mobile Phleboto



screen-print.JPG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Try this

Cursor C1 is
select e.mail_last_name||','||e.first_name as Name,
                     d.area_cd,
          (case when :hdr.division_cd = 'A' then d.division_cd else :hdr.division_cd end)  ||'  '|| e.department_id as div_dept,
           e.employee_id,
                     e.social_security_no,
           e.position_cd,
           p.position_name,
         --e.hours_available
           ds.start_time,
           ds.start_hours||' '||start_minutes as start_tm,
           ds.end_time
  from department d,employee e,dept_staff ds, position p
 where d.department_id = e.department_id
    and e.position_cd = p.position_cd(+)
   and e.social_security_no = ds.social_security_no
   and ds.schedule_date = '25-JUN-2011'
   and ds.site_code = 'CS17';

Author

Commented:
its the same
 one line data
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
Hi, how many records do you get from the query that you use to create the cursor?

e.g:

select count (*) from
(
  select e.mail_last_name||','||e.first_name as Name,
                       d.area_cd,
             d.division_cd||'  '||e.department_id as div_dept,
             e.employee_id,
   ...
)

Author

Commented:
7 records as I indicated above

Author

Commented:
COUNT(*)
7

Author

Commented:
I tried in toad using DBMS

Baines,Nichole
Bekoe,Stephen
Guitierrez,Paige
Johnson,Teressa
King,Sonja
Lemke,Mandy
White,Katrina

Only problem is when I run this code in forms

Commented:
So you are getting the answer when you are running this script in TOAD, but in Oracle forms it's making issue.

Author

Commented:
Yes
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
Think the issue is in your loop, why did you comment out this: -- Exit when C1%notfound; ?

Can you change to this and test it:

exit when C1%NOTFOUND;
-- exit when :SYSTEM.LAST_RECORD = 'TRUE';
-- next_record;

Open in new window

Author

Commented:
still one line data. 1st line is retrieved.
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
Can you change your code to this:

BEGIN
FOR cur_rec in C1
	LOOP
		fetch cur_rec into
		:dtl.emp_name,
		:dtl.area_cd,
		:dtl.div_dept,
		:dtl.emp_id,
		:dtl.social_security_no,
		:dtl.position_cd,
		:dtl.position_name,
		:dtl.start,
		:dtl.start_time,
		:dtl.end_time;
	END LOOP;
END;

Open in new window

Commented:
Comment this line and try

--first_record;
flow01IT-specialist

Commented:
If possible avoid filling your blocks by your self : define the query on block level.

Else (i can't verify it now for myself)
in an empty block there is 1 new record and that is also the last record on the moment you test it
so I think you will need the exit when C1%NOTFOUND  as gerwinjansen posted
The next_record wil probably fail because there is only 1 record.
I suggest replacing the next_record  by a create_record : it wil create that following record and position on it.

Commented:
Try this  method


CURSOR empProfile (emp_details IN VARCHAR2)
IS
SELECT
select e.mail_last_name||','||e.first_name as Name,
                     d.area_cd,
           d.division_cd||'  '||e.department_id as div_dept,
           e.employee_id,
                     e.social_security_no,
           e.position_cd,
           p.position_name,
         --e.hours_available
           ds.start_time,
           ds.start_hours||' '||start_minutes as start_tm,
           ds.end_time
  from department d,employee e,dept_staff ds, position p
 where d.department_id = e.department_id
    and d.division_cd = case when :hdr.division_cd = 'A' then d.division_cd else :hdr.division_cd end
   and e.position_cd = p.position_cd(+)
   and e.social_security_no = ds.social_security_no
   and ds.schedule_date = '25-JUN-2011'
   and ds.site_code = 'CS17';

employee  empProfile%ROWTYPE;



OPEN empProfile(emp_details );
FETCH empProfile INTO empDetails;
IF empDetails%FOUND THEN
  WHILE empDetails%FOUND LOOP

      dtl.emp_name := empDetails.Name
      dtl.area_cd :=empDetails.area_cd;
      dtl.div_dept :=empDetails.div_dept;
      dtl.emp_id :=empDetails.social_security_no;
      dtl.social_security_no := empDetails.
      dtl.position_cd:= empDetails.position_cd;
      dtl.position_name:= empDetails.position_name
      dtl.start:= empDetails.start
      dtl.start_time:= empDetails.tart_time
      dtl.end_time := empDetails.end_time
        FETCH empProfile INTO empDetails;
   END LOOP;
END IF;
CLOSE empProfile ;
Naveen KumarProduction Manager / Application Support Manager

Commented:
Hi Anumoses,

The problem is because of the below.

your code :
==========
Cursor C1 is
select e.mail_last_name||','||e.first_name as Name,
                     d.area_cd,
           d.division_cd||'  '||e.department_id as div_dept,
           e.employee_id,
                     e.social_security_no,
           e.position_cd,
           p.position_name,
         --e.hours_available
           ds.start_time,
           ds.start_hours||' '||start_minutes as start_tm,
           ds.end_time
  from department d,employee e,dept_staff ds, position p
 where d.department_id = e.department_id
    and d.division_cd = case when :hdr.division_cd = 'A' then d.division_cd else :hdr.division_cd end
   and e.position_cd = p.position_cd(+)
   and e.social_security_no = ds.social_security_no
   and ds.schedule_date = '25-JUN-2011'
   and ds.site_code = 'CS17';

Begin

Open C1;
first_record;
loop
Fetch C1 into
:dtl.emp_name,
:dtl.area_cd,
:dtl.div_dept,
:dtl.emp_id,
:dtl.social_security_no,
:dtl.position_cd,
:dtl.position_name,
:dtl.start,
:dtl.start_time,
:dtl.end_time;
Exit when C1%notfound;
exit when :SYSTEM.LAST_RECORD = 'TRUE'; --> The problem lies here. When you said FIRST_RECORD that is the only record you have in the block and after that you are checking whether that is the last record in the block here in the loop and it will give YES and hence it will exit out of the loop.
next_record;
END LOOP;
Close C1;

End;

so comment out this this in your loop code : -- exit when :SYSTEM.LAST_RECORD = 'TRUE'; and this needs to be there uncommented : Exit when C1%notfound;

Even after you do this, if you get some other errors - then we need to analyze and resolve them accordingly to make it work. But yes, this is the problem for the issue which you are seeing now.

Thanks

Author

Commented:
@ gerwinjansen
tried your code..
But now I get only one line data. I get the  last record.
BEGIN
FOR cur_rec in C1
      LOOP
            :dtl.emp_name := cur_rec.mail_last_name;
            :dtl.area_cd  := cur_rec.area_cd;
            :dtl.div_dept := cur_rec.div_dept;
            :dtl.emp_id := cur_rec.employee_id;
            :dtl.social_security_no := cur_rec.social_security_no;
            :dtl.position_cd := cur_rec.position_cd;
            :dtl.position_name := cur_rec.position_name;
            :dtl.start := cur_rec.start_time;
            :dtl.start_time := cur_rec.start_hours;
            :dtl.end_time := cur_rec.end_time;
      END LOOP;
END;
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
Hi Anumoses,

OK, that means the loop is working as I would expect.

What is your :dtl referring to? Is it some sort of pointer to the 1st line in your Oracle Form? If we would be able to increment the position in your form we could fill it the way you want it.

Author

Commented:
If you see my screen print out attached earlier, you will see its the detail block.
IT-specialist
Commented:
did you try to the create_record to create the other records and set the pointer to it ?

now you stay at the first record within the loop and at the end the last fetch data are visable on that first and only record,
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
I think flow01 has a point, please try it (I don't have knowledge of Oracle Forms).

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial