7 Replies Latest reply: May 8, 2014 8:58 AM by Joe Upshaw RSS

    Adding a sequence as a default value to a tabular column field

    Richard Legge

      Hi All,

       

      APEX 4.2.4. Oracle XE 11.2

       

      I'm trying to add a default value from a sequence to a tabular column field, so that it populates when I hit the 'Add Row' button..

       

      The reason for this by the way, rather than just using a database Sequence and trigger on the column (which exists and works fine), is that I'm also trying to add detail records to the row when I create it, and I cannot unless I have the parent ID to pass to the Child records.

       

      Looking at the docs, (for example pl/sql enhancements in oracle 11g), I thought Id have be able to use a PL/SQL expression such as to_char(petty_cash_seq.NEXTVAL) or to_number(petty_cash_seq.NEXTVAL) or any such expression that retrieves the sequence.nextval, as this became available in PL/SQL on Database

       

      so, Ive tried

       

      to_char(petty_cash_seq.NEXTVAL) in tabular form attributes - default column / PL/SQL expression, and it tells me  ORA-02287: sequence number not allowed here...

       

      Ive also tried wrapping it up in a PL/SQL package, as below: 

       

      DECLARE v_seq_value NUMBER; BEGIN select petty_cash_seq.NEXTVAL into v_seq_value from dual;return v_seq_value; END;

       

      and I get ORA-00923: FROM keyword not found where expected...

       

      Can anyone help as to why either of these don't appear to work as an expression / package in the default type??

       

      or..... a simple alternative to get the ID of the next record when I hit 'Add Row' so that I can create child records at the same time...

       

      And I have been mulling over the merits of master / detail integrity, and trying to think of a neat way to create the master, add the details without having to submit the master first, then go back and enter details.... And to put it in context, its an expense application where users create an expense entry, and upload copies of their expenses at the same time...

       

      Many Thanks

      Richard

        • 1. Re: Adding a sequence as a default value to a tabular column field
          scott.wesley

          I just ran through the tabular form wizard and chose to populate my PK with a sequence.

          The declarative result for the ID column definition was

          Primary key souce type: Existing sequence

          Primary key source: my_seq

           

          This was on APEX 4.2.0 - is this suitable for your scenario?

           

          Alternatively, one thought that came to mind was a dynamic action firing when you click "add row" that populated the ID column in new record.

          • 2. Re: Adding a sequence as a default value to a tabular column field
            Richard Legge

            Hi Scott, I did try create the sequence as you describe, but it doesn't populate the field until submit is completed (one could definitely argue that I shouldn't be trying to create child records until the parent is submitted, otherwise there is the possibility of creating orphan records..

             

            The alternative, how to encapsulate creating parent and children into a single transaction... i.e. create an expense entry, upload the supporting paperwork into a child table, and commit the two together...

             

            cheers

            • 3. Re: Adding a sequence as a default value to a tabular column field
              Richard Legge

              A bit more on this..... (and Im still stuck..) Ive seen what would appear to be the answer on other questions, but its not working for me....

               

              Ive created a dynamic action.. On the Dynamic action I have two Actions..

               

              - Action 1, is a bit of PL/SQL that retrieves nextval from the sequence, and populates a page item. This works fine and the item is populated with the sequence nextval when I hit the button.

              - Action 2, is the Add new Row javascript function that adds the new row.

               

              Ive then set the default type on the tabular form to point at the page item... However, when I hit new row, it only populates the field with the first value that it picked up when the page was created and does not increment if I hit the Add Row again..

               

              Ive tried also refreshing the report with a dynamic action.. and then picks up the new value, which you see briefly, but then removes the added row.. (even though the sequence is correct ).

               

              So, I must be missing something.. simply to have a sequence incrment on each new Row on a Tabular Report..

              • 4. Re: Adding a sequence as a default value to a tabular column field
                Mike Kutz

                I'm confused.

                It sounds like you have two tabular forms on the same page.

                Your requirement:  when you add a row to the top (master), you want to add a row to the bottom (details) with some default values and it is already linked to the Master.

                 

                If this is the case, would it be possible for you to create a small demo app on http://apex.oracle.com/ that shows what you are trying to accomplish?

                Make sure you create a developer account so that you don't have to post your email for the spam-bots.

                 

                MK

                • 5. Re: Adding a sequence as a default value to a tabular column field
                  Richard Legge

                  HI Mike. I'll upload an example, but. nope, i don't have two tab forms.. Just the one form With a link on it that takes me to a child page that allows me to enter or update the child records of its parent on the tabular form...

                   

                  id like to be able to hit 'add row' to the tabular form.. which brings up the empty record, including the link column, then enter the required information.. Including going to the child record page Via the link, and entering the child records.

                   

                  HOwever, I cannot generate the sequence in the tabular form when I hit 'add row' so that the link has the value to pass to the the page item with the child form...  (I can get the sequence value as above, but not a new one for each 'add row')

                   

                  IN theory pretty simple... Create a parent, (although not submitted), add the child records, then either submit both as an atomic transaction. (Not possible on the web unless part of the same page, I think), or at least submit the children, move back to the parent to complete the transaction...(and face the risk of having to clean up orphan child records in the event that the parent transaction doesn't get completed...)

                   

                  my alternative is 1) force the user to submit the parent.. Go back and enter the child records.. Which is a bit clunky., or 2)  Create a standard form for the master, with the detail on the same page... (which is also a bit clunky, given that the user is already on the tabular form screen...)

                   

                  Rgds

                  richard

                  • 6. Re: Adding a sequence as a default value to a tabular column field
                    Mike Kutz

                    Create a parent, (although not submitted), add the child records, then either submit both as an atomic transaction. (Not possible on the web unless part of the same page, I think),

                    IMHO, this would be the smart way to do it... "submit both as an atomic transaction".

                    As far as "not possible on the web"... that's why you use APEX Collections.... temporary, inter-page, session based data storage.

                    (think: GTT - but at the Web Session level)

                     

                    You'll use one collection for the parent and one for child.  (Parent would have only one row...)

                     

                    Then, on the "final page", the Submit Process will process both Collections as part of the same transaction.

                    HINT: make sure you "create view" on top of the collections to make it easier to code...

                     

                    You'll probably need to create a lot of custom Processes.  I suggest putting all the PL/SQL in a Package and manage that code with a code repository.

                     

                    MK

                    • 7. Re: Adding a sequence as a default value to a tabular column field
                      Joe Upshaw

                      Use a combination of AJAX and JavaScript to get the next sequence value in an overridden version of Add Row.

                       

                      1. On the page editor, right click on Ajax Callbacks and select Create.

                      2. On the next page, select PL/SQL. Do *not* select the Tabular form. Leave this blank.

                      3. Give your process a name, e.g. getNextSequence

                      4. Add the code (EDITED for your own object names):

                       

                      DECLARE
                          ln_NextSequence NUMBER := 0;
                      BEGIN
                          SELECT MY_SEQUENCE.NEXTVAL
                          INTO ln_NextSequence
                          FROM DUAL;
                          
                          HTP.P(TO_CHAR(ln_NextSequence));
                      END;    

                       

                      5. When you look now, you will see that the Add Row button calls javascript:addRow(). Remove all of this and change the button to be triggered via a dynamic action.

                      6 Create a dynamic action on Click of your Add Row Button

                      7. Add the following JavaScript code to get the new sequence value using your AJAX call from above and then set it on the newly added row:

                       

                      var nextSequenceID;
                      
                      apex.server.process(   'getNextSequence'
                                           , {}
                                           , { dataType: "text",
                                               async: false,
                                               complete: function( ajaxResponse )
                                                        {                                    
                                                          nextSequenceID = ajaxResponse.responseText;
                                                        }
                                             });
                      
                      addRow();
                      
                      $('td [headers="YOUR_ID_COLUMN_NAME_HERE"] input:last').val(nextSequenceID);
                      

                       

                      -Joe