Actually now you can do the same as you would do with triggers in the database. Let's see how to use the new features. Suppose we have a tabular form based on the EMP table and we want to make a validation that commision cannot be null if the job is 'SALESMAN'. The constraint would be:
(job != 'SALESMAN') or (job = 'SALESMAN' and commission is not null)
Follow the steps below to create a validation on the tabular form (assumed that you already made a tabular form based on the EMP table):
- In the validations section, click the add icon
- In the Tabular form select list, select "Tabular Form"
- Select page as the validation level
- In the next step, enter a name for the validation
- Select pl/sql as the validation type
- Select pl/sql expression and click next
- In the validation code text area, enter the following:
(:job != 'SALESMAN') or (:job = 'SALESMAN' and :commission is not null)
- In the error message text area, enter an error message. Like:
Error: if job is Salesman, the commission is mandatory
- Click create validation
Run the page to see if it works. For example look for a row where the job is Salesman and empty the commission field. Click submit to save. APEX should give the error message now.
If there is a row where the job is Salesman and the commission field is empty, you can also change another column, let's say the salary, to get the error message. That is because the execution scope is by default set to created and modifed rows. This also means that you may see rows that are invalid, according to the validation, but no error will show up because those rows are not modified. You can change this by doing the following:
- Go to the application builder
- In the validations section, click on the validation that we just created
- In the conditions section, select "all submitted rows" in the execution scope list box.