ADF - DBSequence Attribute Does Not Refresh on Insert Because of Wrong SQLType

In this post, I would like to describe a special case related to the ADF entity attribute of type DBSequence.

To quote the documentation for DBSequence type from Developing Fusion Web Applications with Oracle Application Development Framework (12.1.2):

When you create a new entity row whose primary key is a DBSequence, a unique negative number is assigned as its temporary value. This value acts as the primary key for the duration of the transaction in which it is created. If you are creating a set of interrelated entities in the same transaction, you can assign this temporary value as a foreign key value on other new, related entity rows. At transaction commit time, the entity object issues its INSERT operation using the RETURNING INTO clause to retrieve the actual database trigger-assigned primary key value. In a composition relationship, any related new entities that previously used the temporary negative value as a foreign key will get that value updated to reflect the actual new primary key of the master.

In my case, for some reason, the DBSequence primary key is defined with the wrong SQLType "FLOAT" instead of "NUMERIC" as shown in the following code snippet:

<Attribute
    Name="Id"
    ColumnName="ID"
    SQLType="FLOAT"
    Type="oracle.jbo.domain.DBSequence"
    DefaultValue="@0"
    ColumnType="NUMBER"
    PrimaryKey="true"
    RetrievedOnInsert="true"
    IsUpdateable="false"

With this definition, the new entity instance can be posted to the database. The primary key can be correctly assigned by the trigger. But the entity instance cannot be updated with the assigned primary key value from the database.

In a composition relationship, the related new entities that use the DBSequence primary key as a foreign key will fail to be posted to the database due to the attempt to insert NULL into the foreign key column. The error message looks like "ORA-01400: cannot insert NULL into (...)".

To fix this issue, correct the SQLType as the following:

<Attribute
    Name="Id"
    ColumnName="ID"
    SQLType="NUMERIC"
    Type="oracle.jbo.domain.DBSequence"
    DefaultValue="@0"
    ColumnType="NUMBER"
    PrimaryKey="true"
    RetrievedOnInsert="true"
    IsUpdateable="false"

You can find the documentation from:

http://docs.oracle.com/middleware/1212/adf/ADFFD/bcentities.htm#ADFFD19777

And here's API for DBSequence:

http://docs.oracle.com/middleware/1212/adf/ADFMR/oracle/jbo/domain/DBSequence.html

ADF: DBSequence attribute got an empty value for a new row

From the section "4.10.10 How to Get Trigger-Assigned Primary Key Values from a Database Sequence" in the documentation "Developing Fusion Web Applications with Oracle Application Development Framework" at:

http://docs.oracle.com/middleware/1212/adf/ADFFD/bcentities.htm#ADFFD19777

We have the following description:

"When you create a new entity row whose primary key is a DBSequence, a unique negative number is assigned as its temporary value. This value acts as the primary key for the duration of the transaction in which it is created. "

But sometimes, for some reason, you could come across the problem that a DBSequence attribute remains empty for a new row. In my case, it happened to an entity newly created after I manually switched Data Type Map of the model project from Java Extended for Oracle to Java.

To fix this problem, you need make sure DefaultValue and Domain properties of the attribute are there as the following highlighted lines:

<Attribute
    Name="ContentItemId"
    Precision="19"
    ColumnName="CNT_ITEM_ID"
    SQLType="NUMERIC"
    Type="oracle.jbo.domain.DBSequence"
    ColumnType="NUMBER"
    TableName="FMM_CNT_ITEM"
    PrimaryKey="true"
    IsUpdateable="while_insert"
    DefaultValue="@0"
    Domain="oracle.jbo.domain.DBSequence"
    RetrievedOnInsert="true">

The domain object and the magic default value "@0" cause the view object to generate a negative number as the temporary key.

You can also find a discussion on this issue at:

https://java.net/projects/adfemg/lists/issues/archive/2013-05/message/64