Oracle ADF Committing master and relevant detail rows simultaneously

There is no doubt that using a trigger to provide unique id's from a database sequence is the best way to handle primary keys. (By the way, I wonder which version of Oracle database will provide an auto-increment data type like MySQL?) There are cases however that require you to create master and detail records in the application server memory and then commit everything simultaneously like in the case of an e-shop where you have to keep both the order header and lines uncommitted until the user finally decides to place the order.

In cases like these, using the DBSequence type for the corresponding entity object attribute might get you into trouble, like I did just yesterday. Theory says that the value of a DBSequence key attribute is a (unique) negative number that later becomes the value assigned by the database trigger. What I did, was try to create detail rows programatically but the master attribute reference for these rows became the negative DBSequence value assigned to the header line at the start of the transaction. So when I tried to do the final commit I got a very clear error explaining that the foreign key constraint for the detail rows was violated.

The only reliable way I found to get myself out of this was to turn back to my old JDeveloper 10g Handbook (published back in 2004) and dig out the following code to allow my entities to obtain an id value directly from the database sequence, thus moving the trigger code from the DB to the application server. So overriding the create method for my custom order header entity object gave me something like this :

import oracle.jbo.server.SequenceImpl;

  ...

  /**
   * Provide a new order id from the SQ_ORDERS database sequence.
   */
  protected void create(AttributeList attributeList)
  {
      super.create(attributeList);
    
      // get a new sequence value for the order ID
      SequenceImpl sqOrders = new SequenceImpl("SQ_ORDERS", getDBTransaction());
      setOrderId( sqOrders.getSequenceNumber());
  }