Avatar of anumoses
anumoses
Flag for United States of America asked on

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

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
Oracle Database

Avatar of undefined
Last Comment
anumoses

8/22/2022 - Mon
ronythom

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

anumoses

ASKER
its the same
 one line data
Gerwin Jansen

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,
   ...
)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
anumoses

ASKER
7 records as I indicated above
anumoses

ASKER
COUNT(*)
7
anumoses

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ronythom

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

ASKER
Yes
Gerwin Jansen

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

Your help has saved me hundreds of hours of internet surfing.
fblack61
anumoses

ASKER
still one line data. 1st line is retrieved.
Gerwin Jansen

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

ronythom

Comment this line and try

--first_record;
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
flow01

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.
ronythom

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 Kumar

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
anumoses

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

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.
anumoses

ASKER
If you see my screen print out attached earlier, you will see its the detail block.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
flow01

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Gerwin Jansen

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

ASKER
thanks