10 Replies Latest reply: Dec 7, 2012 9:19 AM by Arie Geller RSS

    Bug in Tabular form Not Exist Validation, fails when creating multiple rows

    Tauceef
      Hi,

      I have a Not Exist validation created for a Tabular form:
         select 1 from my_table where col1 = :col1 and col2 = :col2
      Execution State: For created and Modified Rows

      which is working fine when a row already there in DB

      and I am trying to enter a new row with same data.

      but when there's no data and I enter two rows with same data, validation does not fire, first row should allowed to enter

      but for second row it should give error that data already exists, which is not happening.

      for example,

      let's say I have a row in DB with: 1, 2, 3

      when I try to add a new row with same data ie. 1, 2, 3 validation fires and showing error, which is fine.

      but let's say there's no data in DB and I am entering two rows with:

      1, 2, 3
      1, 2, 3

      validation is not working this time, it is allowing to enter both the rows.

      do I need to create a manual validation for this or what?

      Apex 4.1.1.00.23

      Regards,
      Tauceef

      Edited by: Tauceef on Dec 6, 2012 10:09 AM

      Edited by: Tauceef on Dec 6, 2012 11:22 AM

      Edited by: Tauceef on Dec 6, 2012 11:27 AM
        • 1. Re: Bug in Tabular form Not Exist Validation fails when creating multiple rows
          Tauceef
          Hello,

          I am able to reproduce the same error in apex.oracle.com also.

          Please see the same in my workspace:

          http://apex.oracle.com/pls/apex/f?p=29420:42

          username: demo
          password: demo

          just try to enter two or more employees with same name, it does not give any error.

          but if any employee already exists and try to enter a new employee with same name or update a employee to any existing name, it displays the error correctly.


          Regards,
          Tauceef

          Edited by: Tauceef on Dec 6, 2012 4:34 PM
          • 2. Re: Bug in Tabular form Not Exist Validation, fails when creating multiple rows
            VC
            Tauceef wrote:
            Hi,

            I have a Not Exist validation created for a Tabular form:
            select 1 from my_table where col1 = :col1 and col2 = :col2
            Execution State: For created and Modified Rows
            I am not sure how this above statement will work for you! because this statement will try to substitute the bind variables for each row and always find a matching row existing in the database.

            And In your validation the check is done against a database table and the new records are not yet populated into the db

            I guess you will need to exclude the current row by checking the ID column of your table
            • 3. Re: Bug in Tabular form Not Exist Validation, fails when creating multiple rows
              Tauceef
              Hello Vikram,

              As I have done in the demo, validating the uniqueness of the Employee Name column only.

              In my original app, I have to check the uniqueness combining some of the columns of a table.

              combining all the columns values should be unique, so have to do like that.

              And as I said, its working when a row exists, but when we enter new values(multiple at a time) that time its not doing the validation.

              The demo I created, you can try entering two or more rows at a time with same name, it will not validate,

              but when you update a column to existing name or enter a existing name, its working fine.

              and if you see the example also while creating the validation, there also they have given like that,
              we can refer a Tabular form column like a bind variable in the validation.

              Regards,
              Tauceef

              Edited by: Tauceef on Dec 6, 2012 6:07 PM
              • 4. Re: Bug in Tabular form Not Exist Validation, fails when creating multiple rows
                VC
                And In your validation the check is done against a database table and the new records are not yet populated into the db, may be try using the apex_application.g_f0X
                • 5. Re: Bug in Tabular form Not Exist Validation, fails when creating multiple rows
                  Tauceef
                  Already did, there's a problem with that, it will check even if I don't update any row.

                  that type of validation should fire only when I update or insert any row, how can I check that??

                  without that check, it will always give error, because it is going to check each and every row and that row is there in DB and will show error.

                  Regards,
                  Tauceef
                  • 6. Re: Bug in Tabular form Not Exist Validation, fails when creating multiple rows
                    Tauceef
                    Any expert please check this.

                    Is this really a bug or I need to create my validation in any other way??

                    Please let me know.

                    Regards,
                    Tauceef
                    • 7. Re: Bug in Tabular form Not Exist Validation, fails when creating multiple rows
                      Marc Sewtz
                      Like Vikram was saying, you can't validate your tabular form data against data that has not been written to the database yet, i.e. in a tabular form validation, you can validate the data that you are submitting from the tabular form, but if you run a query against the underlying table, then this is done prior to applying your changes and inserting any new records. The changes and new rows are not available in the table at that point.

                      So if you want to make sure that a user does not enter the same data twice into different rows, you need to write your own validation or page process which would loop through the apex_application.g_fxx arrays and use your own logic to check for any duplicates.

                      Regards,
                      Marc
                      • 8. Re: Bug in Tabular form Not Exist Validation, fails when creating multiple rows
                        Tauceef
                        Hi Marc,

                        Thanks for the reply.

                        Now I got it, I was under impression that it will validate one by one row before insertion,

                        but it's going to check all the rows first, then do the insertion.

                        Now I created a manual insertion & updation process, and checking inside that only and displaying the message using "apex_application.g_print_success_message"

                        but isn't my point valid, can't it be like that, that it should validate row by row before insertion??


                        Regards,
                        Tauceef
                        • 9. Re: Bug in Tabular form Not Exist Validation, fails when creating multiple rows
                          VC
                          Tauceef,

                          I agree and think it is possible but APEX team should work on adding a new validation rule. May be add it to Official Features Request Application

                          Create an unique constraint on your table and handle the ORA error in your error handling function and return a user friendly error message.

                          http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35125/bldr_attr.htm#sthref672

                          http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_error.htm#BGBEEGIB

                          Thanks,
                          Vikram
                          • 10. Re: Bug in Tabular form Not Exist Validation, fails when creating multiple rows
                            Arie Geller
                            Hello Tauceef,

                            >> Now I got it, I was under impression that it will validate one by one row before insertion,
                            >> but it's going to check all the rows first, then do the insertion.

                            Just to clear things up, the APEX engine works exactly as you describe, but this is your problem.

                            The APEX engine do check\validate all the rows first, one row at a time, and only if all the rows pass validation, the DML processes kick in. The DML processes work on the entire Tabular Form record set (per page view, of course).

                            In order for your type of validation to work (the one that queries the database), as you want it, the validation and DML actions have to run together, row by row. This is not how things work, because it’s less efficient/optimal, and may lead to a situation where a certain row can fail validation, but other row(s) already been inserted/updated/deleted into/from the database. This could be a very tricky situation.

                            Vikram has suggested to rely on a database constraint, and control the APEX error message. This is possible, although I’m not sure that in this case, the Tabular Form will retain all the user changes, in case of validation error. You should check it.

                            Another option, which Marc hinted on, is to manipulate the G_Fxx arrays. In your case, you should loop through the new rows, compute their MD5, and make sure that all the rows have different MD5 values. This is on top of the current validation, which makes sure that the new rows do not already exist in the database.

                            Regards,
                            Arie.

                            -------------------------------------------------------
                            ♦ Please remember to mark appropriate posts as correct/helpful. For the long run, it will benefit us all.

                            ♦ Author of Oracle Application Express 3.2 – The Essentials and More