Wednesday Feb 29, 2012

The infamous Missing IN or OUT parameter error

Often error messages like "attempt to set a parameter name that does not occur in the SQL" and "Missing IN or OUT parameter at index" are caused by developers mixing required and optional bind variables in their SQL queries WHERE clause. If you used bind variables in ADF Business Components SQL queries, make sure the bind variables are set to required. Bind variables that are marked as optional are only good to be used in View Criterias.

In a future version of Oracle JDeveloper, the two bind variable types (the optional ones used in View Criteria and the required ones used in the context of SQL queries) are visually separated in the layout of the View Object editor.

Tuesday Nov 01, 2011

Using ADF logger with Groovy in ADF BC

Groovy is a scripting language that can be used in the context of ADF BC. It simplifies Java object access and method execution. Basically everything you could access from Java you can access from Groovy. If logging is needed in your Groovy scripting, then this can be achieved using the ADF Logger as shown below. Just add script similar to this into the Groovy code are (e..g when working with custom entity validators)

//log information to console. Create a logger instance for
//the entity class – "Employees in this sample

oracle.adf.share.logging.ADFLogger LOGGER =
createADFLogger(source.getClass())"Groovy Log Statement: oldValue and newValue in Validdator
"+oldValue+" ---> "+newValue)"Groovy Log Statement: All salaries in Department
"+Departments1.Employees1.sum('Salary'))"Groovy Log Statement: current budget "+(100000 -
(Departments1.Employees1.sum('Salary')+ newValue) + oldValue ))

Like In Java, you define a variable to hold the logger reference to then use it to print info messages

Using Groovy in Entity Validation

The use case for this sample has been posted on OTN as follows: "How to define entity validation for the Salary attribute of the Employees table so that pay raises going beyond the cumulated salary budget for a departments throws an exception". The logic for this is as follows:

AvailableBudget – (AllSalariesInDepartment + newEmployeeSalar) + currentEmployeeSalary < 0

The available budget is compared to all salaries paid in a department. Chances are that some salaries have been changed for an entity but not yet committed, which means thatyou need to read the salary from the entity cache to obtain the current budget. You then add the old employee salary before subtracted the new salary from the available budget to determine if the pay raise is within budget.

To implement the solution in Groovy, I assume you have an ADF Business Component model built on the Departments and Employees table of the Oracle HR Schema. You should have two entities created with an association defined between.

Select the association and double click on it to show the visual editor. Click onto the Relationship menu item to see the Source and Destination accessor names (e.g. Departments1, Employees1). Using accessors, you can "walk" the entity relationship up and down (e.g. to access the Departments entity from the Employees entity), which is key to this little recipe.

In the Employee entity editor, select the Salary attribute and click the green plus icon next to the Validation section to create a new validation rule.

In the Add Validation Rule for Salary dialog, choose the Rule Type as Script Expression

As the Rule Definition, add the following script:

//perform validation
if((100000 - (Departments1.Employees1.sum('Salary')+ newValue) + 
             oldValue) < 0){
  return false
return true

As you can see from the script, it uses the entity association accessors to get to the Departments entity to then compute the summary of salaries paid within.

What you need to know: The validation provides implicit object to access information contained within. Two of these information is oldValue and newValue, which gives you a handle to the value as it was before (committed or uncommitted) and the new value the attribute is changed to. Another useful object, though not used here is source. The source object gives you access to the entity object itself. You can use this e.g. to access public methods in an entity impl class (no need to expose them on the client interface). A use case for this is if you wanted to access getPostedData which is the data originally queried from the database. The getPostedData is a protected method, which is why you need a custom entity impl class to expose it through a public method, just the need to use the source object.

Select the Failure Handling tab of the dialog to define the message to be shown when validation fails. Add message string with variables. Variables are defined by curly braces and a name, like {newsal}

The message is added to a resource properties file. In the sample I used a message key of: SALARY_TOO_HIGH_ERROR. The message string associated to this in the properties file is shown below:

SALARY_TOO_HIGH_ERROR=Salary raise to {newsal} is too high for money {moneyAvailable} left in the budget for this department. The variable names can then be configured in the Failure Handling tab, e.g. to reference the new value and the budget.

You then run the AM and add salaries so that the budget is existed and the message will show.

Using Groovy for this use case is straight forward though, as I admit, requires some better knowledge of ADF Business Components and Java.

Download: You can download a simple sample from here. Configure the database connect to point to a HR schema and run the Application Module in the tester. Browse the departments and edit employees for a department so the salary exceed the budget value of 100000.

Before you ask: To make the budget generic, you can have an attribute added to the Departments table that holds the value, in which case it too becomes Groovy accessible


A blog on Oracle JDeveloper, ADF, MAF, MCS and other mobile and web topics inspired by questions and answers posted on the OTN forums.

Frank Nimphius


« February 2017