13 Replies Latest reply: Oct 10, 2011 9:22 AM by 774160 RSS

    Avoiding duplicate rows in oracle multirow block Forms

    g_user
      Hi ,

      How to avoid duplicate rows in a multi record block in oracle forms.
      if i check from the table and write the code in when validate item using raise form trigger failure
      it is not allowing me to go to other item .
      help me to over come this
      thanks
      user_g
      Hi, Thanks for the information but like to add some more points for the above
      in form block have values like this
      Here PK column is not displayed hidden , for the other items a row is not allowed to repeat how to achieve this

      col1 col2 col3 col4
      a 10 b 20
      a 10 b 20 --> X not allowed
      a 20 b 30

      also these values are not saved in table
      we have to check in the block

      thanks
      userg

      dear all,

      able to complete the task used a constraint combination of columns in the back end and for col4 i used a hidden item in the block(forms) to control the
      duplicate rows in the block .

      thanks for all the help,
      userg

      Edited by: g_user on Aug 26, 2011 8:16 AM
        • 1. Re: Avoiding duplicate rows in oracle multirow block Forms
          lake
          You are raising the exception while at a duplicate column?
          Well if you don't want the user to have to come up with a different value then I guess you'd have to delete it?
          (delete_record)? I'm having trouble thinking of a way to deal with it.

          What did you want to happen?
          It's raising that exception that causes this. You can just print a message without raising an exception if that works better.
          • 2. Re: Avoiding duplicate rows in oracle multirow block Forms
            lake
            It's weird that the forum software posts things another 2 times when someone posts something when the software is in its
            very slow for some reason state. So then there needs to be a remove all but one of them function in there. Have I missed it? Very ironic considering the discussion was how to detect and stop duplicate records.
            • 3. Re: Avoiding duplicate rows in oracle multirow block Forms
              Christian Erlinger
              lake wrote:
              It's weird that the forum software posts things another 2 times when someone posts something when the software is in its
              very slow for some reason state.
              As you might know, not everything developers implement is bug free, especially when it comes to the developers who implemented jive as it seems ;)

              See
              Posted three times automatically

              You might ask the moderators and have your triplicate posts removed.

              cheers
              • 4. Re: Avoiding duplicate rows in oracle multirow block Forms
                CraigB
                How to avoid duplicate rows in a multi record block in oracle forms.
                This is a fairly common question in the forum. A simple search would have given you your answer.

                Craig...
                • 5. Re: Avoiding duplicate rows in oracle multirow block Forms
                  lake
                  so what is the best solution?
                  this one?
                  duplicate records in  a multi record block
                  Form program unit:
                  
                  function COMPARISON (in1 number, in2 number) is
                  if in1 = in2 then
                  return(1);
                  else
                  return(0);
                  end if;
                  end;
                  
                  3 new hidden fields:
                  
                  CONTROL.PK_COPY
                  
                  DATABLOCK.MATCH_FOUND
                  calculation mode: formula
                  formula: COMPARISON(:control.PK_COPY, :datablock.PK)
                  
                  CONTROL.NUMBER_OF_MATCHES
                  calculation_mode: summary
                  summary_function: Sum
                  summarised_block: DATABLOCK
                  summarised_item: MATCH_FOUND
                  
                  WHEN_VALIDATE_ITEM on DATABLOCK.PK
                  :control.pk_copy := :datablock.pk;
                  if :control.number_of_matches > 1 then
                  message('matching key found');
                  end if;
                  
                  (DATABLOCK must have query_all_records = TRUE)
                  I forgot about that.
                  But why not enhance forms to be able to access multiple records without navigating and incurring the
                  restricted issue? It would really improve the forms experience. It's great that some loopholes have been found like this
                  but it'd be better to let customers deal with these issues in a more straightforward way.
                  • 6. Re: Avoiding duplicate rows in oracle multirow block Forms
                    lake wrote:
                    It's weird that the forum software posts things another 2 times when someone posts something when the software is in its very slow for some reason state. So then there needs to be a remove all but one of them function in there. Have I missed it? Very ironic considering the discussion was how to detect and stop duplicate records.
                    Moderator Action:
                    The duplicate and triplicate posts have been removed, to improve the readability of the thread.

                    The irony was definitely noted.
                    • 7. Re: Avoiding duplicate rows in oracle multirow block Forms
                      CraigB
                      so what is the best solution?
                      I can't answer that - it depends on your situation! I personally prefer a Record Group solution (see: Duplicate Record Checking), but there is a little more code involved. There is also a similar method listed on Francois Degrelle's site (Avoid duplicated records in the same block). Which method is the best is up to you!

                      Craig...
                      • 8. Re: Avoiding duplicate rows in oracle multirow block Forms
                        Christian Erlinger
                        The best solution is to add constraints to your table to enforce the uniqueness of your columns on the lowest level. Every client side check is optional and there to make users life easier. But the real enforcement for uniqueness must be enforced with constraints, as you only check for duplicates in your session. This would not prevent me in another session to enter the very same record at the same time as you, and your record isn't unique anymore.

                        cheers
                        • 9. Re: Avoiding duplicate rows in oracle multirow block Forms
                          CraigB
                          The best solution is to add constraints to your table to enforce the uniqueness of your columns on the lowest level.
                          I agree and disagree with this statement. A database constraint is definately needed, however, in a web deployed form I don't want the network trip to the database to check for uniqueness. Since Forms went to the web, it is better to perform as much checking as it possible in the Form (Client) to reduce network traffic to the database! One of the primary causes of poor performance in a web deployed form is excessive network traffic! See Forms Tuning Techniques - Users will sing yoru praise and Oracle FOrms 10g Tuning Tips for more information! :)

                          Craig...
                          • 10. Re: Avoiding duplicate rows in oracle multirow block Forms
                            Christian Erlinger
                            Of course you can/should do additional validations on the client as needed basing on your requirement, there is nothing wrong with that. If you can't afford the roundtrip to the database than by all means: do everything you can to permit it if it's not allowed anyway.
                            But even if you do checks on the client side you must not forget that you are in a multi user environment and you can't check for duplicates of uncommited data in another session. Also let's not forget that you might not be the only one jamming data into your database, and if you don't enforce it with a constraint another application actually could create duplicates because the developers forgot to implement a unique check. So the only way to be 100% sure you cannot pile duplicates into the database is a constraint.

                            My point is:
                            - always create a constraint when you want to enforce uniqueness => this is a must
                            - if needed do additional checkings on the client to make users life easier => optional and can be implemented as needed

                            From what I see mostly the first point get's ignored, and the second get's implemented. The implementations most of the time utterly fail when I start the application twice and enter the same data in two different sessions. Now I have a serious problem, as data which is supposed to be unique isn't unique anymore. too_many_rows are going to happen in every corner of the application, and nobody knows why.

                            cheers
                            • 11. Re: Avoiding duplicate rows in oracle multirow block Forms
                              g_user
                              Hi Christian erlinger

                              thanks for the valid input.

                              regards,
                              userg
                              • 12. Re: Avoiding duplicate rows in oracle multirow block Forms
                                g_user
                                dear all,

                                able to complete the task used a constraint combination of columns in the back end and for col4 i used a hidden item in the block(forms) to control the
                                duplicate rows in the block .

                                thanks for all the help,
                                userg
                                • 13. Re: Avoiding duplicate rows in oracle multirow block Forms
                                  774160
                                  i worked on the same requirement and performed the same.
                                  But i am getting the error as "FRM-30377: Summary item must reside in single-record block or in same block as summarized item."
                                  The error causing for Control Block