Home » Developer & Programmer » Forms » Looping through block for each record (Oracle forms 11G)
Looping through block for each record [message #671377] Mon, 27 August 2018 04:42 Go to next message
RaShi.Raj
Messages: 81
Registered: May 2014
Location: Bangalore
Member
Hi All,

I have a requirement to loop through all records in a block for each record from the same block. Something like below. Please help.

BEGIN
GO_BLOCK('BLOCK1');
FIRST_RECORD;
LOOP
GO_BLOCK('BLOCK1');
FIRST_RECORD;
LOOP
<business logic>
EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE'
NEXT_RECORD;
END LOOP;

EXIT WHEN:SYSTEM.LAST_RECORD = 'TRUE';
NEXT_RECORD;
END LOOP;

END;



Thanks,
Rashi

[Updated on: Mon, 27 August 2018 04:43]

Report message to a moderator

Re: Looping through block for each record [message #671378 is a reply to message #671377] Mon, 27 August 2018 05:06 Go to previous messageGo to next message
RaShi.Raj
Messages: 81
Registered: May 2014
Location: Bangalore
Member
Sorry, had posted a solution which doesn't seem to be working fine in all scenario.

[Updated on: Mon, 27 August 2018 05:32]

Report message to a moderator

Re: Looping through block for each record [message #671392 is a reply to message #671378] Tue, 28 August 2018 03:07 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why are you using two loops?
Re: Looping through block for each record [message #671394 is a reply to message #671392] Tue, 28 August 2018 05:19 Go to previous messageGo to next message
RaShi.Raj
Messages: 81
Registered: May 2014
Location: Bangalore
Member
cookiemonster wrote on Tue, 28 August 2018 13:37
Why are you using two loops?
Requirement was to find a duplicate value for a field when a button is pressed (couldn't have done while entering values in a block).
This is what I have done and seems to be working fine.

PROCEDURE CHECK_FOR_XXXXX IS

v_item_id table1.item_id%TYPE;
v_dup NUMBER := 0;
v_pos NUMBER ;
BEGIN
/* For every record, loop through all the records */
GO_BLOCK('table_block');
FIRST_RECORD;
v_pos := :SYSTEM.CURSOR_RECORD;
LOOP
v_item_id := :table_block.item_id;
v_dup := 0;

GO_BLOCK('table_block');
FIRST_RECORD;
LOOP
IF (:system.cursor_record <> v_pos AND :table_block.item_id = v_item_id) THEN
v_dup := v_dup + 1;
DISPLAY_ERROR_MSG('Duplicate item id ');
Raise form_trigger_failure;
END IF;
IF :SYSTEM.last_record = 'TRUE' THEN
v_pos := v_pos+1;
EXIT;
ELSE
NEXT_RECORD;
END IF;
END LOOP;

GO_BLOCK('table_block');
GO_RECORD(v_pos);
EXIT WHEN:SYSTEM.LAST_RECORD = 'TRUE';
END LOOP;

END;


Re: Looping through block for each record [message #671397 is a reply to message #671394] Tue, 28 August 2018 08:20 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
You only need the first go_block - the others just seen you to the block you're in anyway (unless display_error_msg changes the block and I doubt that).
The v_dup variable is pointless - you're failing the whole process as soon as it's incremented to 1.

You can do this with a single pass over the block - build up an array of the relevant value(s) as you go then in each record check if the va;ue in the block already exists in the array - if it doesn't, add it, if it does raise an error. It'll be a lot more efficient that what you've got.

This check isn't going to stop users from saving duplicates, you'll need db constraints for that, so why not just rely on them?
Re: Looping through block for each record [message #671398 is a reply to message #671397] Tue, 28 August 2018 09:10 Go to previous messageGo to next message
RaShi.Raj
Messages: 81
Registered: May 2014
Location: Bangalore
Member
Thanks for the reply cookiemonster Smile

Yes, I have now got rid of redundant GO_BLOCK() and variable v_dup too.

"You can do this with a single pass over the block - build up an array of the relevant value(s) as you go then in each record check if the va;ue in the block already exists in the array - if it doesn't, add it, if it does raise an error. It'll be a lot more efficient that what you've got." - I will try this.

"This check isn't going to stop users from saving duplicates, you'll need db constraints for that, so why not just rely on them?" - I have DB constraint too. I initially tried catching ORA-00001 constraint (unique constraint) in ON-ERROR trigger . Is there a way we can do this ? We can catch FRM error messages but not ORA right? I still tried with catching corresponding FRM message associated with ORA-00001 which did display the custom message but eventually exit the form.
Re: Looping through block for each record [message #671399 is a reply to message #671398] Tue, 28 August 2018 09:41 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I believe you can catch ORA in on-error.
I don't have a working forms installation to test.
But I do have the form builder help - which mentions dbms_error_code - have a look at that.
Re: Looping through block for each record [message #671415 is a reply to message #671399] Wed, 29 August 2018 02:45 Go to previous messageGo to next message
RaShi.Raj
Messages: 81
Registered: May 2014
Location: Bangalore
Member
IF dbms_error_code = '-1' THEN
ERROR MESSAGE;
END IF;

You are right. dbms_error_code does catch ORA error. I wasted half a day on work around and not sure how I missed this when googling which is mentioned in aplace.
Thank you so much cookiemonster

Re: Looping through block for each record [message #671416 is a reply to message #671415] Wed, 29 August 2018 03:28 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Form builder help is incredibly comprehensive - look there for what you can do with triggers before searching the web.
That's where I saw dbms_error_code - in the page for the on-error trigger.
Re: Looping through block for each record [message #671417 is a reply to message #671416] Wed, 29 August 2018 03:29 Go to previous message
RaShi.Raj
Messages: 81
Registered: May 2014
Location: Bangalore
Member
Sure...Thanks
Previous Topic: Get current window title
Next Topic: reoprt calling from form 11g
Goto Forum:
  


Current Time: Tue May 26 22:54:09 CDT 2020