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

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';

its the same
 one line data

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,
   ...
)

7 records as I indicated above

COUNT(*)
7

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

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

Yes

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;

Select allOpen in new window

still one line data. 1st line is retrieved.

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;

Select allOpen in new window

Comment this line and try

--first_record;

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.

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 ;

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

@ 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;

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.

If you see my screen print out attached earlier, you will see its the detail block.

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

thanks