Home » Developer & Programmer » Forms » populate multiple rows from a table to a multi record block (forms 10g)
populate multiple rows from a table to a multi record block [message #385887] Wed, 11 February 2009 13:27 Go to next message
vinodkumarn
Messages: 54
Registered: March 2005
Member
I have block A and B with multi record

I enter a value in column1 in block A, then system should query another table based on the entered value, fetch the multiple rows and populate into block B

I have written the below procedure and called in the when validate item trigger for column1 in block A

problem with this is i am able to populate only 1st row into block B, but i have totally 3 rows, 2nd and 3rd records are not populated

can somebody let me know what is the problem

PROCEDURE POPULATE_ROWS IS
cursor c1 is
select code,name,value1,value2
from lookup
where sub_code = :blockA.column1;
BEGIN
go_block('blockB');
for i in c1 loop
:blockB.code := i.code;
:blockB.name := i.name;
:blockB.value1 := i.value1;
:blockB.value2 := i.value2;
exit when c1%notfound;
next_record;
end loop;
END;
Re: populate multiple rows from a table to a multi record block [message #385890 is a reply to message #385887] Wed, 11 February 2009 14:11 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is a classic master-detail design. Therefore, get rid of your PL/SQL solution (i.e. delete what you've done so far and start from scratch). Use data block wizard and create master-detail relationship between these two blocks. Oracle Forms will efficiently do the job.

As of your code, well, EXIT is superfluous in a cursor FOR loop; it will exit by itself.

If you want to know what is happening (i.e. why the second and the third records are not fetched), include MESSAGE into the loop and display one (or many) values cursor had fetched in order to see whether there really are these records or not.
Re: populate multiple rows from a table to a multi record block [message #385894 is a reply to message #385890] Wed, 11 February 2009 14:32 Go to previous messageGo to next message
vinodkumarn
Messages: 54
Registered: March 2005
Member
block A and block B are having master-detail relation

block A is based on table A and block B on table B
and i am trying to default with rows in block B from table lookup

I have removed the exit when c1%notfound line,still result is same
There are really records, bcos when i run the cursor select statement i get 3 records fetched
Re: populate multiple rows from a table to a multi record block [message #385895 is a reply to message #385894] Wed, 11 February 2009 14:45 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, so you have removed EXIT.

What about the rest of my message? If you really have created it as master-detail, you do not need PL/SQL to populate detail block after the master is fetched by a query.

What did MESSAGE reveal?
Re: populate multiple rows from a table to a multi record block [message #385897 is a reply to message #385894] Wed, 11 February 2009 15:09 Go to previous messageGo to next message
vinodkumarn
Messages: 54
Registered: March 2005
Member
when i inserted a message like below after the for loop

for i in c1 loop
message('rows'||' '||i.code);

it did not populate any records onto block B
when i comment the message line, 1st record is populated to block B as earlier

If i put the message before next_record, i am not seeing any message but the result is same as earlier ie only 1st record is fetched

[Updated on: Wed, 11 February 2009 15:12]

Report message to a moderator

Re: populate multiple rows from a table to a multi record block [message #385943 is a reply to message #385897] Wed, 11 February 2009 23:53 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
You are not 'listening'. Start the Form from scratch, use the wizard to create the two blocks, then use the other wizard to create the 'relations'. You do NOT need to write your own code.

David
Previous Topic: How to kill excel.exe from task manager?
Next Topic: Import Excel and numbers in varchar field
Goto Forum:
  


Current Time: Wed Dec 07 04:59:29 CST 2016

Total time taken to generate the page: 0.07982 seconds