OraFAQ Forum: Forms » fetch record in a datablock

Home » Developer & Programmer » Forms » fetch record in a datablock

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

fetch record in a datablock [message #556261]

Fri, 01 June 2012 04:58

kriti.akanksha
Messages: 28
Registered: March 2012

Junior Member

declare
cursor c1 is select distinct  edt, regno, name, desgn, form_no, form_status, recd_on
		          from ol_registration_vu 
                          where RECD_ON between :control.REC_FROM and :control.REC_TO ;
	 
begin
   open c1;
      loop
	fetch c1 into
	    	 :ol_registration.edt,
	    	 :ol_registration.regno,
	    	 :ol_registration.name,
	    	 :ol_registration.desgn,
	    	 :ol_registration.form_no,
	    	 :ol_registration.form_status,
	    	 :ol_registration.recd_on;
	exit when c1%notfound;
	       next_record;
	   end loop;
	close c1;
first_record;	  
end;

this coding giving me only one record i.e the last record of the table, whereas the table has 50 records. how can i get all records from the table in the form datablock.

[EDITED by LF: applied [code] tags]

[Updated on: Fri, 01 June 2012 05:26] by Moderator

Report message to a moderator

Re: fetch record in a datablock [message #556264 is a reply to message #556261]

Fri, 01 June 2012 05:28

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

The code seems to be OK, so the only reason that it returns less records than expected is the WHERE clause.

P.S. Before posting your next message, please, see How to use [code] tags and make your code easier to read?.

Report message to a moderator

Re: fetch record in a datablock [message #556281 is a reply to message #556264]

Fri, 01 June 2012 06:47

kriti.akanksha
Messages: 28
Registered: March 2012

Junior Member

i am getting all records one by one by overwriting the first row only with a prompting error mesage FRM-40102: record must be entered or deleted first.

i want all the records in a tabular form.

Report message to a moderator

Re: fetch record in a datablock [message #556282 is a reply to message #556281]

Fri, 01 June 2012 06:56

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Is there any reason for doing it this way (i.e. in a loop)? Under normal circumstances, you'd create a data block (based on a table), run the form and execute query - it would be populated with data. If necessary, you'd even enter some search criteria (which would evaluate to a WHERE clause, somewhere behind the scene).

It appears that you are fighting Forms instead of making friends with it. Did you ever read any documentation or attended classes or something? If not, perhaps you should because - the way you are (trying to) work with Forms is ... hm.

Report message to a moderator

Re: fetch record in a datablock [message #556454 is a reply to message #556282]

Mon, 04 June 2012 06:43

kriti.akanksha
Messages: 28
Registered: March 2012

Junior Member

sorry for my poor concept but i am totally new in oracle forms and i didn't get any training on this platform.
as you said-- Is there any reason for doing it this way (i.e. in a loop)? Under normal circumstances, you'd create a data block (based on a table), run the form and execute query - it would be populated with data. If necessary, you'd even enter some search criteria (which would evaluate to a WHERE clause, somewhere behind the scene).

before the loop i did it with the same concept(create a data block (based on a table), run the form and execute query) an it was working fine.
but now i want only distinct records from the view as i discussed this on http://www.orafaq.com/forum/t/181586/129190/

Report message to a moderator

Re: fetch record in a datablock [message #556548 is a reply to message #556454]

Tue, 05 June 2012 03:58

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

And Littlefoot provided a link at the end of that thread which shows a way to do what you need.
Obviously you haven't bothered to read it.

The other obvious alternative is to create a new view that does a distinct and base the block on that.

You never need to populate a block the way you are currently trying to do it.

Report message to a moderator

Re: fetch record in a datablock [message #557383 is a reply to message #556261]

Tue, 12 June 2012 06:34

abrarji
Messages: 8
Registered: June 2012
Location: Pakistan

Junior Member

Dear I think you can do it easily...
DECLARE
CURSOR A IS
Select Emp_Cod, Name, Dept, d.Dpt_Des, Desigination From Emp_Info i, Department d
Where i.Dpt_cod = d.dpt_cod
Order By Emp_Cod;
BEGIN
FOR B IN A LOOP
:BLK_NAME.FIELD1 := B.CUR_FIELD1;
:BLK_NAME.FIELD2 := B.CUR_FIELD2;
:BLK_NAME.FIELD3 := B.CUR_FIELD3;
Next_record;
END LOOP;
Next_Record;
END;

Feel free to ask me again....

Report message to a moderator

Re: fetch record in a datablock [message #557388 is a reply to message #557383]

Tue, 12 June 2012 06:45

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Please read and follow How to use [code] tags and make your code easier to read?

I doubt that code is going to fix the problem

Report message to a moderator

Re: fetch record in a datablock [message #557390 is a reply to message #557383]

Tue, 12 June 2012 06:53

kriti.akanksha
Messages: 28
Registered: March 2012

Junior Member

thanks a lot!

now i getting the records in right way (not overwriting in the same row).
but i am getting a error message FRM-40102:Record must be entered or deleted first, then clicking on OK of this message the next row comes in form.

Report message to a moderator

Re: fetch record in a datablock [message #557391 is a reply to message #557390]

Tue, 12 June 2012 07:18

abrarji
Messages: 8
Registered: June 2012
Location: Pakistan

Junior Member

This is level 5 error because it go to next record....
two method for this...
1- Remove Next_Record After Loop
2- On Error Trigger
If Message_Code=40102 and Message_Type='FRM' Then
Null;
End if;

It would not appear...

Abrar

Report message to a moderator

Re: fetch record in a datablock [message #557629 is a reply to message #557391]

Thu, 14 June 2012 06:43

kriti.akanksha
Messages: 28
Registered: March 2012

Junior Member


declare
qry varchar2(5000);
n number;
alert number;

  cursor c1 is select distinct  edt, regno, name, desgn, form_no, form_status, recd_on
		             from ol_registration_vu where RECD_ON between :control.REC_FROM and :control.REC_TO ;
		             
		             
  cursor c2 is select distinct  edt, regno, name, desgn, form_no, form_status, recd_on
		             from ol_registration_vu where  regno= :control.v_regno  or form_no= :control.v_form_no ;
		          		             
		          
 Begin
  if :control.rg='B' then
        
   if :control.REC_FROM is not null and :control.REC_TO is not null  then
          
    
            IF :control.v_form_status is not null then
		 qry := 'RECD_ON between :control.REC_FROM and :control.REC_TO and a.form_status=:control.v_form_status ';
		        	 set_block_property('ol_registration', default_where, qry);
	            	go_block('ol_registration');
	            	clear_block(no_validate);
	        -- 	message('1111'); message('');
		             execute_query;
	        	END IF;
	--	message('bbbb'); message('');
		        IF :control.HOUSE_TYPE is not null then
			        qry := 'RECD_ON between :control.REC_FROM and :control.REC_TO and b.qtr_type=:control.house_type ';
			         set_block_property('ol_registration', default_where, qry);
		            go_block('ol_registration');
		            clear_block(no_validate);
		        --message('2222'); message('');
		             execute_query;
	        	END IF; 
   -- message('cccc'); message('');
		        IF :control.HOUSE_TYPE is not null and :control.v_form_status is not null then
			        qry := 'RECD_ON between :control.REC_FROM and :control.REC_TO and qtr_type=:control.house_type and form_status=:control.v_form_status ';
			         set_block_property('ol_registration', default_where, qry);
	            	go_block('ol_registration');
	            	clear_block(no_validate);
	         --	message('3333'); message('');
	              	execute_query;
		        END IF;	
--	message('dddd'); message('');	 
		------
			IF :control.status is not null then
					qry := 'RECD_ON between :control.REC_FROM and :control.REC_TO ';
            
	       	IF :control.status='edt' then
					qry :=qry|| ' order by EDT ';
					ELSIF :control.status='form_no' then
					qry :=qry|| ' order by FORM_NO ';
					ELSIF :control.status='name' then
					qry :=qry|| ' order by NAME ';
					ELSIF :control.status='recd_on' then
					qry :=qry|| ' order by RECD_ON ';
		    	END IF;
	     else
				qry:=qry||' ORDER BY EDT,NAME,FORM_NO,RECD_ON ';
				 set_block_property('ol_registration', default_where, qry);
	      	go_block('ol_registration');
	      	clear_block(no_validate);
	    -- message('4444'); message('');
	        	execute_query;
				
			END IF;
  
     -- message('eeee'); message('');  
         go_block('ol_registration');
             clear_block(no_validate);
              first_record;
                open c1;
                 loop
	                fetch c1 into
	    	           :ol_registration.edt,
	    	           :ol_registration.regno,
	    	           :ol_registration.name,
	    	           :ol_registration.desgn,
	    	           :ol_registration.form_no,
	    	           :ol_registration.form_status,
	    	           :ol_registration.recd_on;
			 	           exit when c1%notfound;
			 	          next_record;
	             end loop;
	          close c1;
          first_record;
      
 	else
				message('Please fill Received from and Received to date(s).'); message('.'); 
	  	raise form_trigger_failure;
end if;	


elsif :control.rg='S' then
if :control.v_regno is not null or :control.v_form_no is not null then
	
	 
	go_block('ol_registration');
             clear_block(no_validate);
              first_record;
                open c2;
                 loop
	                fetch c2 into
	    	           :ol_registration.edt,
	    	           :ol_registration.regno,
	    	           :ol_registration.name,
	    	           :ol_registration.desgn,
	    	           :ol_registration.form_no,
	    	           :ol_registration.form_status,
	    	           :ol_registration.recd_on;
			 	           exit when c2%notfound;
			 	          next_record;
	             end loop;
	          close c2;
          first_record;
end if;
end if;
END;

this is my coding, in this cursor c1 and c2 is working fine but all the coding doing before it which retrive data through set_block_property('blockname', default_where, qry); execute_query;
is not working as the conditions given in IF statement. it giving the data same as from cursor c1.

CM: fixed the code tags. They need to be wrapped in square brackets []

[Updated on: Thu, 14 June 2012 07:10] by Moderator

Report message to a moderator

Re: fetch record in a datablock [message #557631 is a reply to message #557629]

Thu, 14 June 2012 07:14

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

The code says:
1) execute a query
2) clear the block
3) loop over c1
4) Repopulate the block with data from c1

It's doing exactly what you told it to.
Why is c1 even there?

Report message to a moderator

Re: fetch record in a datablock [message #557928 is a reply to message #557631]

Mon, 18 June 2012 00:22

kriti.akanksha
Messages: 28
Registered: March 2012

Junior Member

code
declare
qry varchar2(5000);
n number;
alert number;

cursor c1 is select distinct edt, regno, name, desgn, form_no, form_status, recd_on
from ol_registration_vu where RECD_ON between :control.REC_FROM and :control.REC_TO
ORDER BY :control.status ;

cursor c2 is select distinct edt, regno, name, desgn, form_no, form_status, recd_on
from ol_registration_vu where regno= :control.v_regno or form_no= :control.v_form_no ;

cursor c3 is select distinct edt, regno, name, desgn, form_no, form_status, recd_on
from ol_registration_vu where RECD_ON between :control.REC_FROM and :control.REC_TO
and form_status= :control.v_form_status ORDER BY :control.status ;

Begin
if :control.rg='B' then
---------------------------------------------------
if :control.REC_FROM is not null and :control.REC_TO is not null then
--------------------

--------------------
IF :control.HOUSE_TYPE is not null and :control.v_form_status is null then
qry := 'RECD_ON between :control.REC_FROM and :control.REC_TO
and qtr_type=:control.house_type ORDER BY :control.status ';
set_block_property('ol_registration', default_where, qry);
go_block('ol_registration');
clear_block(no_validate);

execute_query;
END IF;
--------------------
--------------------
IF :control.HOUSE_TYPE is not null and :control.v_form_status is not null then
qry := 'RECD_ON between :control.REC_FROM and :control.REC_TO
and qtr_type=:control.house_type and form_status=:control.v_form_status
ORDER BY :control.status';
set_block_property('ol_registration', default_where, qry);
go_block('ol_registration');
clear_block(no_validate);

execute_query;
END IF;
--------------------

-------------------
IF :control.HOUSE_TYPE is null and :control.v_form_status is null then
go_block('ol_registration');
clear_block(no_validate);
first_record;
open c1;
loop
fetch c1 into
:ol_registration.edt,
:ol_registration.regno,
:ol_registration.name,
:ol_registration.desgn,
:ol_registration.form_no,
:ol_registration.form_status,
:ol_registration.recd_on;
exit when c1%notfound;
next_record;
end loop;
close c1;
first_record;
END IF;
-------------------
-------------------
IF :control.v_form_status is not null and :control.HOUSE_TYPE is null then
go_block('ol_registration');
clear_block(no_validate);
--first_record;
open c3;
loop
fetch c3 into
:ol_registration.edt,
:ol_registration.regno,
:ol_registration.name,
:ol_registration.desgn,
:ol_registration.form_no,
:ol_registration.form_status,
:ol_registration.recd_on;
exit when c3%notfound;
next_record;
end loop;
close c3;
first_record;
END IF;

--------------------

------------------
else
message('Please fill Received from and Received to date(s).'); message('.');
raise form_trigger_failure;
end if;

------------------------------------------------

elsif :control.rg='S' then

if :control.v_regno is not null or :control.v_form_no is not null then
go_block('ol_registration');
clear_block(no_validate);
first_record;
open c2;
loop
fetch c2 into
:ol_registration.edt,
:ol_registration.regno,
:ol_registration.name,
:ol_registration.desgn,
:ol_registration.form_no,
:ol_registration.form_status,
:ol_registration.recd_on;
exit when c2%notfound;
next_record;
end loop;
close c2;
first_record;
end if;
end if;
END;

/code

in this form i have a (sorted by) list item, the items in the list item are form_no, name, regno, edt and one blank. when i select form_no in list item, then the data should be come in order by form_no and same for the other items and when i select blank in list item data should come in order by form_no, edt, regno, name. how i get the result in order by if i select any item in list item.

Report message to a moderator

Re: fetch record in a datablock [message #557930 is a reply to message #557928]

Mon, 18 June 2012 00:59

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

SET_BLOCK_PROPERTY + its ORDER_BY clause. Then re-execute query in that block.

You were already told that such an unformatted code is difficult to read. Please, be so kind, spend no more than 10 seconds of your precious time, click here and improve your posting technique.

Report message to a moderator

Re: fetch record in a datablock [message #557949 is a reply to message #557391]

Mon, 18 June 2012 04:22

kriti.akanksha
Messages: 28
Registered: March 2012

Junior Member

declare
 cursor c1 is select distinct  edt, regno, name, desgn, form_no, form_status, recd_on
		             from ol_registration_vu where RECD_ON between :control.REC_FROM and :control.REC_TO 
		             ORDER BY :control.status;

begin
	IF :control.HOUSE_TYPE is null and :control.v_form_status is null then
   	
         go_block('ol_registration');
             clear_block(no_validate);
               first_record;
                open c1;
                
                 loop
	                fetch c1 into
	    	           :ol_registration.edt,
	    	           :ol_registration.regno,
	    	           :ol_registration.name,
	    	           :ol_registration.desgn,
	    	           :ol_registration.form_no,
	    	           :ol_registration.form_status,
	    	           :ol_registration.recd_on;
			 	           exit when c1%notfound;
			 	          next_record;
	             end loop;
	          close c1;
          first_record;
    END IF;
end;

thanks for your help. now i am getting data in sorted form through that list item but only in set_block_proerty part. i want the same for the data coming through cursor c1 and c3 also. what should i do for this, as ORDER BY :control.status is not working here.

Report message to a moderator

Re: fetch record in a datablock [message #557968 is a reply to message #557949]

Mon, 18 June 2012 06:23

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Try something like this (in cursor's declaration section):

cursor c1 is select ...
order by case when :control.status = 1 then edt
              when :control_status = 2 then regno
              else name
         end;

Report message to a moderator

Re: fetch record in a datablock [message #558034 is a reply to message #557968]

Tue, 19 June 2012 00:40

kriti.akanksha
Messages: 28
Registered: March 2012

Junior Member

cursor c1 is select distinct  edt, regno, name, desgn, form_no, form_status, recd_on
		            from ol_registration_vu where RECD_ON between :control.REC_FROM and :control.REC_TO
		             --ORDER BY EDT,NAME,FORM_NO,RECD_ON ; 
		            order by case when :control.status = 'edt' then 'edt'
                              when :control.status = 'name' then 'name'
                              when :control.status = 'form_no' then 'form_no'
                              when :control.status = 'recd_on' then 'recd_on'
                              else 'name'
                              end ;

this order by is not doing anything in form, i think there is some problem with case statement.
please help me.

Report message to a moderator

Re: fetch record in a datablock [message #558042 is a reply to message #558034]

Tue, 19 June 2012 01:02

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

case when :control.status = 'edt' then 'edt'

Red painted part should be a COLUMN name, not a string.

Report message to a moderator

Re: fetch record in a datablock [message #558056 is a reply to message #558042]

Tue, 19 June 2012 01:40

kriti.akanksha
Messages: 28
Registered: March 2012

Junior Member

i tried this before as
order by case
when :control.status = 'edt' then edt
when :control.status = 'name' then name
when :control.status = 'form_no' then form_no
when :control.status = 'recd_on' then recd_on
else name
end ;

but that was showing an error
type mismatch found at 'EDT' among result expression in a CASE expression

Report message to a moderator

Re: fetch record in a datablock [message #558075 is a reply to message #558042]

Tue, 19 June 2012 04:45

kriti.akanksha
Messages: 28
Registered: March 2012

Junior Member

cursor c4 is select distinct  edt, regno, name, desgn, form_no, form_status, recd_on
		           from ol_registration_vu where RECD_ON between :control.REC_FROM and :control.REC_TO
		            order by case 
		                          when :CONTROL.STATUS = 'edt' then edt
                              when :CONTROL.STATUS = 'name' then name
                              when :CONTROL.STATUS = 'form_no' then form_no
                              when :CONTROL.STATUS = 'recd_on' then recd_on
                              when :CONTROL.STATUS = '' then name
                              [i]--else edt,name, form_no, recd_on[/i]
                              end ;

with this i am getting error: type mismatch found at 'EDT' among result expression in a CASE expression.

and when this code with else edt,name, form_no, recd_on, i am getting error:
encountered symbol "," while expecting one of these........
but what i know we can use multiple columns in order by statement.

please give me a solution.

Report message to a moderator

Re: fetch record in a datablock [message #558106 is a reply to message #558075]

Tue, 19 June 2012 07:24

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If it won't work that way (because of datatype mismatch), maybe you should use ref cursor instead. See this discussion (especially its last post).

Report message to a moderator

Previous Topic:	How to use duplicate record built in
Next Topic:	Inserting Multiple Records when we Press Button In the FOrms

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 03 01:13:17 CST 2025