10 Replies Latest reply on Jul 15, 2013 9:28 AM by dare

    How to use Sequence in Forms

    1007980

      Hi,

      I have an Empno Column, At the time of Opening a Form The Empno Will be displayed by using Sequence.

      If the User doesn't save the Form Then the Empno Generated number will be changed.

      How to write the code to get sequence numbers at Empno columns by using Sequence?

       

       

      --

      Thanks & Regards,

      Hari Babu

        • 1. Re: How to use Sequence in Forms
          dare

          If you're using Oracle Sequence, the proper call is:

            SELECT sequence_name.nextval into :block_name.item_name FROM dual;

          • 2. Re: How to use Sequence in Forms
            SatishKumar N

            Hi HariBabu,

            You can use this Below Groovy in EmployeeViewObject for Emplyee ID :

             

            (new oracle.jbo.server.SequenceImpl("COUNTRY_ID_SEQ",adf.object.getDBTransaction())).getSequenceNumber();

             

            WithRegards,

            satishkumarN

            • 3. Re: How to use Sequence in Forms
              1007980

              hi,

              i wrote same thing, that's not my issue.

              If we are not saving the form also another sequence number will be generated. it will destroy the sequence of empno's

              like the values of empno

              999

              1000

              1001

              1002

              1003

              .....

              ...

               

               

              1st if the form is opened, the empno item will show 999. then i am closing the form and opened the form again then the empno will show 1000. Now i am saving the form. In the database the empno will store 1000. This is illegal. I need the empno in database like the above values...................

              • 4. Re: How to use Sequence in Forms
                François Degrelle

                Hello,

                 

                You can put the Oracle sequence.nextval in the initial value of the TextItem.

                 

                Francois

                • 5. Re: How to use Sequence in Forms
                  dare

                  Ok, I understand now your question. Since the oracle sequence is commited in a separate transaction, it is not possible to get the no-gap sequence, considering your example. In the other hand, if you don't commit immediately, than two concurrently opened forms could use the same sequence number, so you'll get the duplicate key!

                   

                  If you're sure that this couldn't happen, you can manage your own sequencing, storing them in another table. Let's say you reached seq 999: entering the form, you'll read from your sequence table 1000, increment to 1001, and assign it to empno, but without commiting. Next time, you'll get the same number, until you commit form, and at the same time, change in seq table. So another will get 1001.

                   

                  Downside of this approach are, as mentioned before, concurrent sessions, which will get the same sequence.

                  • 6. Re: How to use Sequence in Forms
                    1007980

                    Hi,

                    Could u please send me some piece of code..

                    Please Please....

                     

                     

                    With Regards,

                    Hari Babu

                    • 7. Re: How to use Sequence in Forms
                      InoL

                      Many have asked this question before, and the answer will remain the same: bad idea. You can never guarantee a range without gaps. Certainly not with a sequence, and many cases not with your own sequence table that is used concurrently by several sessions.

                       

                      >This is illegal

                       

                      Tom Kyte has a good answer to that here: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1508205334476

                       

                      the requirement is generally found "to not be really a requirement" in *all* of my experiences. even where people tell me "its the law" (found not really to be true).
                      • 8. Re: How to use Sequence in Forms
                        dare

                        You can follow the link that InoL gave, but here's the concept:

                         

                        create table seqtable(seqname, seqno);

                        insert into seqtable(seqname, seqno) values('myempseq', 1);

                         

                        in your form read the next value:

                        select seqname into :block,item from seqtable where seqname = 'myempseq';

                        increment for next read:

                        update seqtable set seqno = seqno + 1 where seqname = 'myempseq';

                         

                        In case that you leave form without commit, you should rollback that update...

                         

                        Again, as said before, this is a disaster if two concurent sessions get the same sequence. In order to avoid that you must develop a strategy of serialization, which is highely unrecommended. Read that articel on asktom site.

                        • 9. Re: How to use Sequence in Forms
                          Christian Erlinger

                          You can follow the link that InoL gave, but here's the concept:

                          You forgot to lock your sequence record before fetching it, and thus you will get duplicates with your concept. Either you serialize as shown in the asktom article in order to prevent that or you forget about the manual ugly number generator alltogether and use a sequence. Everything else will put you in danger.

                           

                          cheers

                          • 10. Re: How to use Sequence in Forms
                            dare

                            That was just an example of how to do it. You're right, but also, I want to discourage usage of that concept. If someone still want to use it, he could just add an "for update" clause in that select satement that fetches next sequence number:

                            select seqno into :block,item from seqtable where seqname = 'myempseq' for update of seqno;

                            that will lock that row, as you pointed out.