This content has been marked as final. Show 4 replies
Firstly, the PL/SQL syntax there is quite a way off...
DECARE should come before BEGIN.
You can't assign values straigh from a SQL query like that, you would need to use a SELECT ... INTO ... FROM ... statement.
Assignments in PL/SQL are := and not =
The condition in the IF statement should be = and not :=
Apart from that, yu should use a SQL exists query validation type rather than PL/SQL returning boolean and then you could just write a SQL statement! :)
Thanks....i tried the SQL Exist method but seems the validation is not getting active...every time i am entering the value in the item and then checking it in the table(through validation) by knowing that there exists a value equal to it the things are still not working
I have set type as exists
validation expression1: select 2 from my_table where my_column = :p2_my_item
where i have replaced the my tags with the actual names
Always execute is set to NO
There are two different condition to consider - execution and validation.
Regarding 'Execution', you will need to clarify what you mean by "Always Execute is set to NO". Do you mean that the conditional execution parameter is set to "Never"? Or is it just a case of the Validation requiring a specific button press or submit event to occur first? Make sure that the conditional criteria are set up correctly as per your requirements - presumably when an update is made to a field or a button is pressed. If it's set to 'Never', it won't fire, ever (naturally!).
As far as the Validation part is concerned, you need to tell us what you are validating against - I say 'against' because you only want the validation to 'activate' (halt post-submission processing) upon a failure. In other words, if the validation action criteria evaluates to 'true', it won't activate.
So, if you want the validation to fail if the tuple exists in the table already (i.e. if you are attempting to enter a 'duplicate'), you ought to choose "not exists" in the validation type. I suspect you are thinking along the lines of the opposite - that the validation 'activates' if it evaluates to true.