This content has been marked as final. Show 10 replies
I am able to reproduce the same error in apex.oracle.com also.
Please see the same in my workspace:
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.
Edited by: Tauceef on Dec 6, 2012 4:34 PM
Tauceef wrote: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.
I have a Not Exist validation created for a Tabular form:
Execution State: For created and Modified Rows
select 1 from my_table where col1 = :col1 and col2 = :col2
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
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.
Edited by: Tauceef on Dec 6, 2012 6:07 PM
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.
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.
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??
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.
>> 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.
♦ 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