Overview
In Oracle Apex, The validation is used to validate the business logic on the submission of the page. Oracle Apex has different types of in-build validation with it. The dynamic actions are used to check some conditions or business logic before submitting the page. Different types of in-build dynamic actions are available with different events.
Technologies and Tools Used
The following technology has been used to achieve the expected output.
- JavaScript
- PL/SQL
- Oracle Apex
Use Case
If you have the requirement to check the business logic before submitting the page or on change of the item. Then, we can use the dynamic actions to achieve this.
This document explains how we can use the dynamic action to do the validations like in-build validation.
Architecture
The following steps explain in detail,
Scenario 1: Create the validation for the Not null or Mandatory field.
Step 1:
Create the page item or form. In the page item, create the dynamic action for the mandatory field in the Lose focus event as below.
Step 2:
Create action as Execute JavaScript as a screenshot with the below code.
var a = $v(‘P18_ENAME’);
if (a == “”) {
apex.message.clearErrors();
if ($(“#P18_ENAME_error”).length == 0) {
apex.message.showErrors([
{type: “error”,location: [“page”, “inline”],
pageItem: “P18_ENAME”,
message: “Employee Name Should have some Values.”,
unsafe: false}])}
} else {
apex.message.clearErrors();
}
Scenario 2: Create the validation for the unique check.
Step 1:
Create the P0_VALIDATION item on the global page and set the value protected as No.
Step 2:
Create dynamic action for unique check in the Lose Focus event.
Step 3:
Create true action for execute server code as a screenshot with the below code.
DECLARE
lv_cnt NUMBER;
BEGIN
SELECT Count(*)
INTO lv_cnt
FROM emp
WHERE Upper(ename) = Upper(:P18_ENAME);
IF lv_cnt = 0 THEN
:P0_VALIDATION := ‘N’;
END IF;
IF lv_cnt > 0 THEN
:P0_VALIDATION := ‘Y’;
END IF;
EXCEPTION
WHEN no_data_found THEN
:P0_VALIDATION := ‘N’;
END;
Step 4:
Create another true action to execute the JavaScript as below screenshot with the below code.
var a = $v(‘P0_VALIDATION’);
if (apex.item(“P18_ENAME”).isChanged()) {
if (a == “Y”) {
apex.message.clearErrors();
$(“#P0_VALIDATION”).val(“N”);
apex.message.showErrors([
{type: “error”,
location: [ “page”, “inline” ],pageItem: “P18_ENAME”,
message: “Employee Name must be unique.”,unsafe: false
}]) }
else{
if ( $(“#P18_ENAME_error”).length == 0) {
apex.message.clearErrors();
$(“#P0_VALIDATION”).val(“N”);
} }}
Screenshot
By using the above method we can add validation to the dynamic action.
Output:
Scenario 1:
Scenario 2:
