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.

Saturday Nov 05, 2011

Accessing View Object UI hints from EL

View Objects can have UI hints defined in their General category. For example, you may want to set the Display Name, Display Name (Plural) and Description on Employees View Object that is based on an Employees entity representing the Employees table in the HR database sample schema. The page snippet below shows how you can access the UI hints on a View Object to display them a header information on an page. 

<af:panelGroupLayout id="pgl2" layout="horizontal">
<af:outputText value="#{bindings.allEmployeesIterator.hints['label']}," id="ot3"/>
<af:spacer width="10" height="10" id="s1"/>
<af:outputText value="#{bindings.allEmployeesIterator.hints['labelPlural']}, " id="ot2"/>
<af:spacer width="10" height="10" id="s2"/>
<af:outputText value="#{bindings.allEmployeesIterator.hints['TOOLTIP']} " id="ot1"/>

So the Display Name property maps to UI hints "label", the Display Name (Plural) property to the UI hints "labelPlural" and the Description property to the "TOOLTIP" string.

These strings are defined in the oracle.jbo.AttributeHints class. For future-save programming you should consider reading the UI hint strings directly from this class.

To do so, expose the AttributeHints class to EL through a helper managed bean (put it to application scope for best performance) that exposes JavaBean properties for each of the strings.

Using such a helper bean, the above page snippets would look similar to this

<af:panelGroupLayout id="pgl2" layout="horizontal">
<af:outputText value="#{bindings.allEmployeesIterator.hints[helperBean.label]}," id="ot3"/>
<af:spacer width="10" height="10" id="s1"/>
<af:outputText value="#{bindings.allEmployeesIterator.hints[helperBean.labelPlural]}, "
<af:spacer width="10" height="10" id="s2"/>

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


« March 2015