9 Replies Latest reply: May 4, 2012 2:47 AM by Prabodh RSS

    Default value for tabular form item

    Cashy
      Hi All,

      I have a tabular form which contains a column that I want to set as a default to: 'TAG'||lpad(XXMEL_VMI_RCV_LINE_TAG_S.NEXTVAL,7,0) when the user presses the 'Add Row' button.

      I have tried just entering it as normal PL/SQL Expression or Function just as it is above and as a SELECT FROM dual but for some reason it is not liking it.

      Can someone please advise?

      I am using apex v4.0.0

      Thanks
      Chris
        • 1. Re: Default value for tabular form item
          VC
          Cashy wrote:
          Hi All,

          I have a tabular form which contains a column that I want to set as a default to: 'TAG'||lpad(XXMEL_VMI_RCV_LINE_TAG_S.NEXTVAL,7,0) when the user presses the 'Add Row' button.

          I have tried just entering it as normal PL/SQL Expression or Function just as it is above and as a SELECT FROM dual but for some reason it is not liking it.

          Can someone please advise?

          I am using apex v4.0.0

          Thanks
          Chris
          Hi Chris,

          What do you mean by it is not liking it ?

          Can you create an example on apex.oracle.com

          Thanks
          • 2. Re: Default value for tabular form item
            Cashy
            Hi Cherukuri,

            When I use *'TAG'||lpad(XXMEL_VMI_RCV_LINE_TAG_S.NEXTVAL,7,0)* as a default with the type PLSQL expression or function

            I get the following error:

            failed to parse SQL query:
            ORA-02287: sequence number not allowed here

            When I use SELECT 'TAG'||lpad(XXMEL_VMI_RCV_LINE_TAG_S.NEXTVAL,7,0) from dual

            I get the following error:

            failed to parse SQL query:
            ORA-00936: missing expression

            I am not sure if the defaults in tabular forms for fields that are not the primary keys on the table can be used in the same way that a text item can be set for example.

            Thanks
            Chris
            • 3. Re: Default value for tabular form item
              VC
              Hi Chris,

              Just create a database function that returns the string that you want and use that function in the column default, that will work perfectly.
              create or replace function retuenDefault return number as
                v varchar2(10);
              begin
                select XXMEL_VMI_RCV_LINE_TAG_S.NEXTVAL into v from dual;
                v := 'TAG' || lpad(v, 7, 0);
                return v;
              end;
              Now use retuenDefault in your column default with Default Type as PLSQL Expression or Function

              Thanks
              • 4. Re: Default value for tabular form item
                Prabodh
                Firstly,
                SELECT 'TAG'||lpad(XXMEL_VMI_RCV_LINE_TAG_S.NEXTVAL,7,0) from dual
                will give the error that you see if you are using this syntax in a PL/SQL block. That is because PL/SQL block expects a INTO in SELECT statement.

                Tabular Form rows are populated by fetching the rows from a table, so you can specify a default value , say using NVL, in the SELECT of the Tabular Form.

                However, populating the field in a Added Row is a completely different story. The empty row is only added in the DOM in the browser, it is not fetched and there is no submit /refresh either. So it will be somewhat difficult to assign the default value for the following reasons
                a) There is no Row/Cell identifier to get a handle on the cell
                b) There is no easy mechanism to trap the addRow() JS function firing.

                Regards,
                • 5. Re: Default value for tabular form item
                  Cashy
                  Hi Cherukuri,

                  Thanks for that.

                  The only issue with that is if the user clicks "Add Row" once and submits then it works fine and the next value is returned from the sequence

                  If they press "Add Row" before submitting the first row, the new value returned in the row has the same value as the first.

                  I would have thought that the first click of the "Add Row" would call the sequence and then subsequent clicks would just keep returning the next value but it doesnt seem to be doing that which is a bit confusing.

                  Thanks
                  Chris
                  • 6. Re: Default value for tabular form item
                    Cashy
                    Hi Prabodh ,

                    Thanks for the explanation.

                    Chris
                    • 7. Re: Default value for tabular form item
                      VC
                      Hi Chris,

                      I would also recommend Prabodh's suggestion to use your own js function that calls addrrow and then populates the default value.

                      Thanks
                      • 8. Re: Default value for tabular form item
                        Cashy
                        Thanks for you input guys,

                        I am by no means an expert when it comes to jQuery so I used a PLSQL trigger instead:

                        CREATE OR REPLACE TRIGGER XXMEL_VMI_RCV_LINE_TMP_T1
                        BEFORE INSERT ON XXMEL_VMI_RCV_LINE_TMP
                        REFERENCING NEW as NEW OLD as OLD
                        FOR EACH ROW

                        DECLARE

                        v_tag VARCHAR2(50);

                        BEGIN

                        IF :NEW.tag_id IS NULL
                        THEN

                        SELECT 'TAG'||lpad(XXMEL_VMI_RCV_LINE_TAG_S.NEXTVAL,7,0)
                        INTO v_tag
                        FROM dual;

                        :NEW.tag_id := v_tag;

                        END IF;

                        END XXMEL_VMI_RCV_LINE_TMP_T1;

                        Not as elegant as I have no doubt jQuery would have been but it works OK.

                        Thanks

                        Edited by: Cashy on 04-May-2012 00:26
                        • 9. Re: Default value for tabular form item
                          Prabodh
                          Good thing that you changed your original requirement and implemented what was practical.

                          Now, of course, you cannot see the default value in new rows added when you click the button as was originally implied. The values are set in the onsubmit processing and hence go to the table/column and show only when the row in next fetched / page is refreshed.

                          Regards,