Wednesday, January 9, 2013

LOV for Editable Primary Key in ADF Table Problem

Probably every ADF developer at least ones was facing this problem - creating LOV for editable primary key attribute rendered in ADF table. Very often database design is based on business key and there is requirement to create LOV for that key. Unfortunately ADF doesn't handle this case well - LOV defined for editable key in the table is unstable. There are different workarounds, but all of them with the side effects. The best workaround is to fix database design and introduce surrogate key for the table - but in practice is hardly possible, no one wants to change DB design, especially if it works well in Oracle Forms.

My idea was to upload sample application with this use case into Oracle Cloud. Everyone could access and test it, I'm sure most of you had reproduced same error in your projects. Ideally - Oracle ADF should handle LOV for editable primary key. Please access live sample application on Oracle Cloud and see it yourself - EditableTableKeyApp on Oracle Cloud. Same application can be downloaded - EditableTableKeyApp.zip.

Here is how you can test it live. Open application on Oracle Cloud, you should get Employees table displayed:


Open EmployeeId LOV - you will see Departments data, this is correct. I was using numeric Department Id to return into Employee Id - this is just for test, to avoid numeric data duplication. Select value from LOV and try to return it:


Infamous error will be generated - Target Unreachable, identifier 'row' resolved to null:


This happens only for LOV defined on primary key attribute. LOV defined for non key attributes works well in ADF table.

This problem is not reproduced with ADF form, same LOV works:


Value was returned without issues:


This is why I was proposing option to use ADF form for editing data from the table - CRUD Operations in Oracle ADF 11g Table Without Auto PPR.

By the way - Oracle Java Cloud runs with JDBC XA driver, this is not compatible with ADF BC for Rollback operation. If you click Undo button and invoke Rollback operation - your data will disappear:


Click again Undo couple of times - data will be back.

In this sample I was using LOV defined on primary key attribute and rendered with editable table:

3 comments:

| ٌُ DaRwiESH ٌُ | said...

please i hope that if you can post topic of creating ADF Servlet filter because i searced and i found no way to find it
i want to prevent anonymous access to all pages in application without ADF security
just filters and beans ...Thanks in advance

Jang-Vijay Singh said...

Hi Andrejus, I'm sorry I may not have understood - did you propose a workaround for the problem or does your sample workspace only show that the problem occurs?
I have had this issue and so far the solution has been to define a surrogate key.
JV

Andrej Baranovskij said...

Hi,

Idea of this post is to show a problem. Yes, most reliable workaround is to define a surrogate key, as you do.

Andrejus