Wednesday, April 14, 2010

Implementing Custom Generic PL/SQL Validator in Oracle ADF

In Oracle ADF you are not constrained only by built in validation rules, its possible to add new ones. This allows to build custom generic validation rules and use them across project through standard JDeveloper wizard. Today I will explain how you can define such rules, and will use example where PL/SQL function will be invoked to check validation logic.

Download sample application - This sample implements custom validation rule, where it invokes PL/SQL validation logic for Number type attribute. In PL/SQL function value is checked to be positive, if not - FALSE is returned and validation rule fails.

To define custom validation rule - open Project Properties for Model project and navigate to Registered Rules section under Business Components group. Here you will be able to define new validation rules for your project:

New rule is created in Java class, this class must be specified together with package name and rule display name:

Once new rule is defined in Model project properties, this rule will become available through JDeveloper Validation Rule wizard - custom SalaryValidation rule is defined for Salary attribute in Employees entity:

When custom rule is selected, it brings Rule Definition tab - nothing important here:

On the next tab - Validation Execution, as with built in validation rule, you can specify conditional execution expression:

In Failure Handling tab, failure message is defined to inform user about validation failure:

Now let's look into validator code - validate() method is invoked automatically by the framework. Using input parameter type - JboValidatorContext, I can access current transaction and call PL/SQL function. Based on the result, if it is negative - ValidationException is thrown and user is informed about failure:

PL/SQL function I'm using in this example is really simple - it accepts NUMBER type parameter and if its value is negative, it returns FALSE:

On runtime, validation is successful for positive salary value:

It brings failure message for negative salary value, based on result from PL/SQL function:

The same rule can be reused in other EO's, this means PL/SQL will be always invoked from one central place - custom validator class.


Zee said...

Great post.

Til said...

thanks a lot for this
but i have two questions
first : i want to know how to validate from managed bean .

second : i posted question on OTN
and asked about
I want to know how to select data from table in backing bean according to primary key i have
the problem is that
i have a table Employee_Salary contains Employee ids and their salary

Empoloyee_Salary table

Employee_ID Number
Employee_salary Number

And Another table Called Employees

Employees table

Employee_ID Number
IsManager Varchar2 its value is [*Yes or NO*]

and other columns that i don't care about this table

i have on a jsff page an this table is editable this is the Empoloyee_Salary table
i want to check before save or after insert if this employee is Manager [from Employees tabke(yes or no)] the salary
cannot be less that 100
i want to know how to make this how to select the value from employees table according to the id i have in the employee_salary table how to make this and make this validation

i have to select IsManager from Employees Table to see if this manager or no
i want to know how to make this in a bean

i use jdeveloper 11g
and my project is ADF Fusion project
and the page that have the Emplpyee_Salary table is JSFF

thanks in advance

Andrej Baranovskij said...


Its a bad practice to validate in a Bean. There should be no any business logic in the Bean. Model should implement validation rules and protect data integrity.


Til said...

thanks Andrejus Baranovskis for reply but i habe a problem in validation and i want your help

i have a table that have two column
in jsff page af:table

the first column contains select one choice list

the second column contains input text with value

i want not to able to select some values from list if the second column contains
"x" value

for example the list contains values
i want not to be able to select deferred if the input text field in the same table contains x

how can i do this

thanks in advance

Anonymous said...

Great post. I'm trying to do something similar - but with a transient VO. I also want to have conditional execution of the validator depending on another vo attribute. Is this possible? Since it's not an EO, it's not giving me conditional execution tab..
thanks, K

Anonymous said...

Hi Andrejus,

This is very helpful information.

But i get an SQL error insufficient privilege to access function.

Apparently it is missing some grants in order to successfully execute the function. Do you know a way how i could get username of the db user which is used to execute the function from the class.

So i could grant him the execute privileges.

Andrej Baranovskij said...

This will be a user, you are using to connect through WebLogic Data Source. Typically HR user for HR schema.


Anonymous said...

Hi andrejusb,
This is very helpfull to learners.