6 Replies Latest reply: Apr 8, 2013 8:24 AM by user404 RSS

    Get next row number in view link

    974835
      Hi,

      I'm using jdeveloper 11.1.2.3.0

      I have a form with viewLink table.
      When the user clicks on CreateInsert to the viewLink table, I need to fill automatically the new row number.
      It can't come from sequence, because it's a viewLink so each group has her internal count.

      for example:
      group 22 has row numbers 1,2,3
      group 33 has row numbers 1,2
      and so on.

      I would like to know what is the best way to get the next number in a group on CreateInsert.
      Of course I can get it with sql like this:
      select max(codes.row_number) + 1
      from codes
      where codes.group = :p_group
      but where and how should I implemet it?

      Thanks a lot!!
        • 1. Re: Get next row number in view link
          1000985
          try trigger on database , after insert do what you want
          • 2. Re: Get next row number in view link
            Timo Hahn
            User, I don't see a reason why you can't get the numbers from a (in your case multiple) sequence. You sql (last+1) will not work in a multi use environment. This has been discussed multiple times here. There are no gap less numbers in such an environment.

            I would go with the sequence and groovy (http://tompeez.wordpress.com/2011/09/02/using-groovy-expression-to-set-a-primary-key-with-a-sequence-number/) this technique can be used for any number field.

            Timo
            • 3. Re: Get next row number in view link
              user404
              Timo Hahn wrote:
              User, I don't see a reason why you can't get the numbers from a (in your case multiple) sequence. You sql (last+1) will not work in a multi use environment. This has been discussed multiple times here. There are no gap less numbers in such an environment.

              I would go with the sequence and groovy (http://tompeez.wordpress.com/2011/09/02/using-groovy-expression-to-set-a-primary-key-with-a-sequence-number/) this technique can be used for any number field.

              Timo
              Just to comment on the gapless part.
              It is possible if necessary:
              http://andrejusb.blogspot.be/2013/01/how-to-implement-gapless-sequence-in.html
              • 4. Re: Get next row number in view link
                Timo Hahn
                @user404 The concept don't guarantee a gap less sequence as it does not handle the case that after you get the next number from the sequence the transaction fails (e.g. network failure). The sequence number is gone and you don't get it again, that the way sequences work. Next time you insert you have a gap.

                Timo
                • 5. Re: Get next row number in view link
                  974835
                  But if i'm getting max(last) +1 only before commit, it shouldn't make troubles.
                  I only want to know how to implement getting max(last) + 1.
                  in which function and how?
                  • 6. Re: Get next row number in view link
                    user404
                    971832 wrote:
                    But if i'm getting max(last) +1 only before commit, it shouldn't make troubles.
                    I only want to know how to implement getting max(last) + 1.
                    in which function and how?
                    Like Timo explained, it probably won't give problems when developing but in multi-user environment it could (and probably will).

                    Example: User one get the value 25 using your max+1. But at the same time (before User1 commits) User2 gets the same value.
                    So when User1 commits, It will work fine but when User2 want's to commit he will receive an error since he also got 25.