Create PL/SQL Validation

In this topic, you create two validations on the Employees page. The first validation checks to make sure the Manager doesn't report to him/her self. The second validation checks to make sure the hire date is less than todays date.


On the Page 2 Properties window, under Page Processing, right-click Validating and select Create Validation.




Make sure Page item is selected and click Next.




Select P2_MANAGER_ID from the list of Page items and click Next.




Enter something like 'Manager not report to self' for Validation Name and click Next. Note that you will receive the error message inline at the field location as well as at the top of the window.




Select PL/SQL for validation type and click Next.




Select Function Returning Boolean and click Next..




Enter the following code in the Validaton Code area, specify an error message, select Yes for Always Execute and click Create Validation.


if :P2_MANAGER_ID != :P2_EMPLOYEE_ID then 
   return true;
else
   return false;
end if;
          



Your validation was created. Click Run.




Select the same last name of the employee you are editing and click Apply Changes.




The error message is displayed inline as well as next to the field. You want to create another validation. Click the Edit Page 2 button in the Developer Toolbar.




You want to create another validation which will make sure that the hire date is before today's date. Under Page Processing, right-click Validating and select Create Validation.




Make sure Page Item is selected and click Next.




Select the P2_HIRE_DATE page item and click Next.




Enter Hire Date must be before today for Validation Name and select Inline with Field for Error Display Location. This time the error message is only displayed next to the field itself. Then click Next.




Select SQL for validation type and click Next.




Select SQL Expression for type of validation and click Next.




Enter TO_DATE(:P2_HIRE_DATE,'DD-MM-YYYY') < SYSDATE for Validation Code, enter some message in the Error Message field and select Yes for Always Execute and click Next.




You only want this validation to execute if the button pressed is Create or Apply Changes (which is a SAVE request). Select Request Is Contained within Expression 1 for Condition Type and enter CREATE,SAVE for Expression 1 and click Create Validation.




Your validation was created. Click Run.




Change the year to 2013 and click Apply Changes.




The error is displayed.