This discussion is archived
13 Replies Latest reply: Oct 10, 2011 7:22 AM by 774160 RSS

Avoiding duplicate rows in oracle multirow block Forms

867671 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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
    rukbat Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    867671 Newbie
    Currently Being Moderated
    Hi Christian erlinger

    thanks for the valid input.

    regards,
    userg
  • 12. Re: Avoiding duplicate rows in oracle multirow block Forms
    867671 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points