Using Groovy Expression to set a Primary Key with a Sequence Number

Just set up a workspace for JDeveloper to show how to use a Groovy expression to set the primary key of an entity object. Chris Muir blogged about this back in 2009 here ADF BC: Using Groovy to fetch sequence numbers for EO/VO attribute default values. There exists a white paper Introduction to Groovy Support in JDeveloper and Oracle ADF 11g which covers Groovy support in JDeveloper.
So, I don’t cover the basic here but only show how to use this in a sample application. The application uses the HR schema and allows you to insert a new employee. The workspace which you can download (see at the end of this blog) is build using JDeveloper
I use the EMPLOYEES_SEQ defined in the HR schema to set the PK of the new employee, EMPLOYEE_ID to the next available sequence number.

Employee Sequence

Employee Sequence

Now we can open the Employee EO from the model layer. Double click on the Employees EO to open the properties inspector for the EO and select the ‘Attribute’ section.
Employees Attributes

Employees Attributes

Now select the EmployeeId in the attributes to get to the attributes properties.
EmployeeId Properties

EmployeeId Properties

Here we can add a default value as literal, expression or as SQL. We select the ‘Expression’ radio button and click on hte pencel on the right side of the input field.
Edit Expression Editor

Edit Expression Editor

Here we enter the Groovy expression to get the next sequence number

(new oracle.jbo.server.SequenceImpl("EMPLOYEES_SEQ",adf.object.getDBTransaction())).getSequenceNumber()

After submitting the dialog with OK you should set the ‘Refresh Expression Value’ to ‘true’ and the ‘Updatable’ LOV to ‘While New’.

Finished Dialog

Finished Dialog

A word of caution here: JDev saves the setting for the refresh condition in the xml file, but the next time you open the dialog again the ‘Refresh Expression Value’ value is gone! I’ll file a bug for this later.
This wraps up the the model layer of the app. You can test your work with the Application Module Tester. When you create a new record you’ll see that the EmployeeId is set to the next sequence number.
Oracle ADF Model Tester

Oracle ADF Model Tester

The ViewController project is pretty simple. It consists of an ADF form with with navigation buttons and a button to call a bounded task flow to create the new employee.
Task Flows

Task Flows

The bounded task flow first calls the CreateInsert operation to create a new record which is then displayed in the form. As you notice, the EmployeeId is an af:outputText element, so that you can’t change it.
Running Application

Running Application

The workspace for JDeveloper can be downloaded from here: Workspace
After downloading the file, remove the suffix ‘.doc’ and rename it to ‘’, as the file is a Zip file.

6 thoughts on “Using Groovy Expression to set a Primary Key with a Sequence Number

  1. Hi,
    I am getting the following error:
    <ADF: Adding the following JSF error message: startup failed, 1: expecting anything but ''\n''; got it anyway @ line 1, column expecting anything but ''\n''; got it anyway @ line 1, column 81.

    I have given the groovy exp as (new oracle.jbo.server.SequenceImpl("MTRNG_SPID_BATCH_REF_SEQ",adf.object.getDBTransaction())).getSequenceNumber()

    and on my AMImpl I have the following code

    ViewObjectImpl spidbatchvo = getSPIDbatchRefVO1();
    ViewObjectImpl spidresultvo = getSearchSPIDVO1();
    Iterator itr = spidlkup.iterator();
    Iterator itr1=priority.iterator();
    while(itr.hasNext() && itr1.hasNext()){
    BigDecimal spid = (BigDecimal);
    String Prio=(String);
    Row r = spidbatchvo.createRow();
    r.setAttribute("SpidLkupSk", spid);
    r.setAttribute("VacSurveyBatchSk", batchId);
    r.setAttribute("CreatedBy", userName);//user name
    r.setAttribute("CreatedDt", ((DBTransactionImpl)getDBTransaction()).getCurrentDbTime());
    // r.setAttribute("UpdatedBy", "TestUser");
    // r.setAttribute("UpdatedDt", ((DBTransactionImpl)getDBTransaction()).getCurrentDbTime());
    r.setAttribute("DeleteFlag", 1);

    I am getting the above mentioned exception at the line:
    Row r = spidbatchvo.createRow();

    Any pointers ?

  2. Hi Timo,

    I am trying to give the groovy expression into the default Value of the EO by selecting Expression Radio button. But it is not accepting the value. Like , even after entering the value and pressing OK, the field remains empty. Somehow after adding the expressionand tabbing out the field contains the expression. But I cannot set the refresh Expression Value to true because it is disabled.
    I am using Jdeveloper 12c.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s