Friday, November 13, 2015

Composite Associations and oracle.jbo.InvalidOwnerException



Consider the following simple Parent/Child scenario:


As shown in this UI, the requirement is simple: we should be able to submit both parent and child records together. I know you will be saying everything is out of the box in this case, but there are still some facts that I was not aware of and would like to share. Before going further, let me share how I created this UI.

1.      Used the following DB script to create the master/child table:

CREATE TABLE lk_dept (
  department_id   NUMBER(4,0) PRIMARY KEY ,
  department_name VARCHAR2(30) ,
  manager_id      NUMBER(6,0) ,
  location_id     NUMBER(4,0) ,
  created_by VARCHAR2(30) ,
  creation_date DATE ,
  updated_by VARCHAR2(30) ,
  update_date DATE ,
  object_vn number
);

CREATE TABLE lk_emp (
  employee_id   NUMBER(6,0)  PRIMARY KEY,
  first_name    VARCHAR2(20) ,
  last_name     VARCHAR2(25) ,
  department_id NUMBER(4,0) ,
  created_by VARCHAR2(30),
  creation_date DATE,
  updated_by VARCHAR2(30),
  update_date DATE,
  object_vn NUMBER
);

ALTER TABLE lk_emp
  ADD CONSTRAINT emp_dept_fk1 FOREIGN KEY (
    department_id
  ) REFERENCES lk_dept (
    department_id
  );           


Generated the BC4J components for the model layer, and dropped them on the Home.jspx page so here is how the project looks like:



By mistake all the EOs/VOs got created in the same folder but its not a show stopper so moving on. Here is how the EmpDeptFK1Assoc looks like to begin with:



Will start with non-composite association between the Dept and Emp table.

And here is how the Home.jspx page looks like:

<?xml version='1.0' encoding='UTF-8'?>
<jsp:root xmlns:jsp="http://java.sun.com/JSP/Page" version="2.1" xmlns:f="http://java.sun.com/jsf/core"
          xmlns:af="http://xmlns.oracle.com/adf/faces/rich">
    <jsp:directive.page contentType="text/html;charset=UTF-8"/>
    <f:view>
        <af:document title="Home" id="d1">
            <af:messages id="m1"/>
            <af:form id="f1">
                <af:panelStretchLayout id="psl1">
                    <f:facet name="bottom"/>
                    <f:facet name="center">
                        <af:panelSplitter id="ps1" orientation="vertical">
                            <f:facet name="first">
                                <af:panelCollection id="pc1">
                                    <f:facet name="menus"/>
                                    <f:facet name="toolbar">
                                        <af:toolbar id="t2">
                                            <af:button actionListener="#{bindings.Commit.execute}" text="Commit Master" id="b5"/>
                                            <af:button actionListener="#{bindings.CreateInsert.execute}"
                                                       text="New Department" disabled="#{!bindings.CreateInsert.enabled}"
                                                       id="b1"/>
                                            <af:button actionListener="#{bindings.Delete.execute}"
                                                       text="Delete Department"
                                                       disabled="#{!bindings.Delete.enabled}" id="b4"/>
                                        </af:toolbar>
                                    </f:facet>
                                    <f:facet name="statusbar"/>
                                    <af:table value="#{bindings.LkDeptVO1.collectionModel}" var="row"
                                              rows="#{bindings.LkDeptVO1.rangeSize}"
                                              emptyText="#{bindings.LkDeptVO1.viewable ? 'No data to display.' : 'Access Denied.'}"
                                              rowBandingInterval="0"
                                              selectedRowKeys="#{bindings.LkDeptVO1.collectionModel.selectedRow}"
                                              selectionListener="#{bindings.LkDeptVO1.collectionModel.makeCurrent}"
                                              rowSelection="single" fetchSize="#{bindings.LkDeptVO1.rangeSize}" id="t1"
                                              partialTriggers="::b1 ::b4">
                                        <af:column headerText="#{bindings.LkDeptVO1.hints.DepartmentId.label}" id="c1">
                                            <af:inputText value="#{row.bindings.DepartmentId.inputValue}"
                                                          label="#{bindings.LkDeptVO1.hints.DepartmentId.label}"
                                                          required="#{bindings.LkDeptVO1.hints.DepartmentId.mandatory}"
                                                          columns="#{bindings.LkDeptVO1.hints.DepartmentId.displayWidth}"
                                                          maximumLength="#{bindings.LkDeptVO1.hints.DepartmentId.precision}"
                                                          shortDesc="#{bindings.LkDeptVO1.hints.DepartmentId.tooltip}"
                                                          id="it1">
                                                <f:validator binding="#{row.bindings.DepartmentId.validator}"/>
                                                <af:convertNumber groupingUsed="false"
                                                                  pattern="#{bindings.LkDeptVO1.hints.DepartmentId.format}"/>
                                            </af:inputText>
                                        </af:column>
                                        <af:column headerText="#{bindings.LkDeptVO1.hints.DepartmentName.label}"
                                                   id="c2">
                                            <af:inputText value="#{row.bindings.DepartmentName.inputValue}"
                                                          label="#{bindings.LkDeptVO1.hints.DepartmentName.label}"
                                                          required="#{bindings.LkDeptVO1.hints.DepartmentName.mandatory}"
                                                          columns="#{bindings.LkDeptVO1.hints.DepartmentName.displayWidth}"
                                                          maximumLength="#{bindings.LkDeptVO1.hints.DepartmentName.precision}"
                                                          shortDesc="#{bindings.LkDeptVO1.hints.DepartmentName.tooltip}"
                                                          id="it2">
                                                <f:validator binding="#{row.bindings.DepartmentName.validator}"/>
                                            </af:inputText>
                                        </af:column>
                                        <af:column headerText="#{bindings.LkDeptVO1.hints.ManagerId.label}" id="c3">
                                            <af:inputText value="#{row.bindings.ManagerId.inputValue}"
                                                          label="#{bindings.LkDeptVO1.hints.ManagerId.label}"
                                                          required="#{bindings.LkDeptVO1.hints.ManagerId.mandatory}"
                                                          columns="#{bindings.LkDeptVO1.hints.ManagerId.displayWidth}"
                                                          maximumLength="#{bindings.LkDeptVO1.hints.ManagerId.precision}"
                                                          shortDesc="#{bindings.LkDeptVO1.hints.ManagerId.tooltip}"
                                                          id="it3">
                                                <f:validator binding="#{row.bindings.ManagerId.validator}"/>
                                                <af:convertNumber groupingUsed="false"
                                                                  pattern="#{bindings.LkDeptVO1.hints.ManagerId.format}"/>
                                            </af:inputText>
                                        </af:column>
                                        <af:column headerText="#{bindings.LkDeptVO1.hints.LocationId.label}" id="c4">
                                            <af:inputText value="#{row.bindings.LocationId.inputValue}"
                                                          label="#{bindings.LkDeptVO1.hints.LocationId.label}"
                                                          required="#{bindings.LkDeptVO1.hints.LocationId.mandatory}"
                                                          columns="#{bindings.LkDeptVO1.hints.LocationId.displayWidth}"
                                                          maximumLength="#{bindings.LkDeptVO1.hints.LocationId.precision}"
                                                          shortDesc="#{bindings.LkDeptVO1.hints.LocationId.tooltip}"
                                                          id="it4">
                                                <f:validator binding="#{row.bindings.LocationId.validator}"/>
                                                <af:convertNumber groupingUsed="false"
                                                                  pattern="#{bindings.LkDeptVO1.hints.LocationId.format}"/>
                                            </af:inputText>
                                        </af:column>
                                    </af:table>
                                </af:panelCollection>
                            </f:facet>
                            <f:facet name="second">
                                <af:panelCollection id="pc2">
                                    <f:facet name="menus"/>
                                    <f:facet name="toolbar">
                                        <af:toolbar id="t4">
                                            <af:button actionListener="#{bindings.Commit.execute}" text="Commit Child" id="b6"/>
                                            <af:button actionListener="#{bindings.CreateInsert1.execute}"
                                                       text="New Employee"
                                                       disabled="#{!bindings.CreateInsert1.enabled}" id="b3"/>
                                        </af:toolbar>
                                    </f:facet>
                                    <f:facet name="statusbar"/>
                                    <af:table value="#{bindings.LkEmpVO2.collectionModel}" var="row"
                                              rows="#{bindings.LkEmpVO2.rangeSize}"
                                              emptyText="#{bindings.LkEmpVO2.viewable ? 'No data to display.' : 'Access Denied.'}"
                                              rowBandingInterval="0"
                                              selectedRowKeys="#{bindings.LkEmpVO2.collectionModel.selectedRow}"
                                              selectionListener="#{bindings.LkEmpVO2.collectionModel.makeCurrent}"
                                              rowSelection="single" fetchSize="#{bindings.LkEmpVO2.rangeSize}" id="t3"
                                              partialTriggers="::b3">
                                        <af:column headerText="#{bindings.LkEmpVO2.hints.EmployeeId.label}" id="c5">
                                            <af:inputText value="#{row.bindings.EmployeeId.inputValue}"
                                                          label="#{bindings.LkEmpVO2.hints.EmployeeId.label}"
                                                          required="#{bindings.LkEmpVO2.hints.EmployeeId.mandatory}"
                                                          columns="#{bindings.LkEmpVO2.hints.EmployeeId.displayWidth}"
                                                          maximumLength="#{bindings.LkEmpVO2.hints.EmployeeId.precision}"
                                                          shortDesc="#{bindings.LkEmpVO2.hints.EmployeeId.tooltip}"
                                                          id="it5">
                                                <f:validator binding="#{row.bindings.EmployeeId.validator}"/>
                                                <af:convertNumber groupingUsed="false"
                                                                  pattern="#{bindings.LkEmpVO2.hints.EmployeeId.format}"/>
                                            </af:inputText>
                                        </af:column>
                                        <af:column headerText="#{bindings.LkEmpVO2.hints.FirstName.label}" id="c6">
                                            <af:inputText value="#{row.bindings.FirstName.inputValue}"
                                                          label="#{bindings.LkEmpVO2.hints.FirstName.label}"
                                                          required="#{bindings.LkEmpVO2.hints.FirstName.mandatory}"
                                                          columns="#{bindings.LkEmpVO2.hints.FirstName.displayWidth}"
                                                          maximumLength="#{bindings.LkEmpVO2.hints.FirstName.precision}"
                                                          shortDesc="#{bindings.LkEmpVO2.hints.FirstName.tooltip}"
                                                          id="it6">
                                                <f:validator binding="#{row.bindings.FirstName.validator}"/>
                                            </af:inputText>
                                        </af:column>
                                        <af:column headerText="#{bindings.LkEmpVO2.hints.LastName.label}" id="c7">
                                            <af:inputText value="#{row.bindings.LastName.inputValue}"
                                                          label="#{bindings.LkEmpVO2.hints.LastName.label}"
                                                          required="#{bindings.LkEmpVO2.hints.LastName.mandatory}"
                                                          columns="#{bindings.LkEmpVO2.hints.LastName.displayWidth}"
                                                          maximumLength="#{bindings.LkEmpVO2.hints.LastName.precision}"
                                                          shortDesc="#{bindings.LkEmpVO2.hints.LastName.tooltip}"
                                                          id="it7">
                                                <f:validator binding="#{row.bindings.LastName.validator}"/>
                                            </af:inputText>
                                        </af:column>
                                        <af:column headerText="#{bindings.LkEmpVO2.hints.DepartmentId.label}" id="c8">
                                            <af:inputText value="#{row.bindings.DepartmentId.inputValue}"
                                                          label="#{bindings.LkEmpVO2.hints.DepartmentId.label}"
                                                          required="#{bindings.LkEmpVO2.hints.DepartmentId.mandatory}"
                                                          columns="#{bindings.LkEmpVO2.hints.DepartmentId.displayWidth}"
                                                          maximumLength="#{bindings.LkEmpVO2.hints.DepartmentId.precision}"
                                                          shortDesc="#{bindings.LkEmpVO2.hints.DepartmentId.tooltip}"
                                                          id="it8">
                                                <f:validator binding="#{row.bindings.DepartmentId.validator}"/>
                                                <af:convertNumber groupingUsed="false"
                                                                  pattern="#{bindings.LkEmpVO2.hints.DepartmentId.format}"/>
                                            </af:inputText>
                                        </af:column>
                                    </af:table>
                                </af:panelCollection>
                            </f:facet>
                        </af:panelSplitter>
                    </f:facet>
                    <f:facet name="start"/>
                    <f:facet name="end"/>
                    <f:facet name="top">
                        <af:panelGroupLayout id="pgl1">
                            <af:button actionListener="#{bindings.Commit.execute}" text="Commit All" id="b2"/>
                        </af:panelGroupLayout>
                    </f:facet>
                </af:panelStretchLayout>
            </af:form>
        </af:document>
    </f:view>
</jsp:root>

The scenarios covered in this blog are:

For Non-Composite association, when auto-submit property of the Department ID field of the master table is set as either true or false, then what behavior is observed for ‘New Employee’, ‘Delete Department’, ‘Commit All’, ‘Commit Master’, and ‘Commit Child’ button.

For Composite association, when auto-submit property of the Department ID field of the master table is set as either true or false, then what behavior is observed for ‘New Employee’, ‘Delete Department’, ‘Commit All’, ‘Commit Master’, and ‘Commit Child’ button.

Scenario 1: Non-Composite Association

                Case 1: Department ID field (PK of the master table) on the master table is not configured with Auto Submit to true


The Department ID field is the PK for Dept table so when autosubmit property is set to false, this is what I observed while creating a new Emp record:

Observation 1: On clicking the New Employee button once, the Employee table refreshes/flickers but a new record is not inserted.  On clicking the New Employee button again only I see a new row in the Employee Table:



Now, after filling in all the details for Employee, we have 3 scenarios to take care:

Observation 2:  Data is saved using Commit All button.
When the Commit All button is clicked in this scenario, the rows in both the table are saved successfully in the DB.

Observation 3:  Data is saved using Commit Master button.
In this case, only record in the Dept table is saved to the DB, so only the container in which the Commit operation is there, that container’s data is saved.

Observation 4:  Data is saved using Commit Child button.
In this case, both Dept and Emp data is saved successfully in the database.

Case 2:  Department ID field on the master table is configured with Auto Submit to true.



Observation 1: Now, since autosubmit is set to true for Master table’s Department Id table, as soon as I click the New Employee button, the new record is visible in the Employee table. So this differs from the previous case.
Observation 2: Data is saved using Commit All button. (Same as Case 1>Observation 2 )
Observation 3: Data is saved using Commit Master button (Same as Case 1>Observation 3 )
Observation 4: Data is saved using Commit Child button (Same as Case 1>Observation 4 )

Scenario 2: Composite Association

Now, the association looks like:



Case 1: Department ID field on the master table is not configured with Auto Submit to true.

Observation 1: In this case, as soon as I click the New Employee button, I get the following exception at the UI:



oracle.jbo.InvalidOwnerException: JBO-25030: Detail entity LkEmpEO with row key null cannot find or invalidate its owning entity.
                at oracle.jbo.server.EntityImpl.internalCreate(EntityImpl.java:1494)
                at oracle.jbo.server.EntityImpl.create(EntityImpl.java:936)
                at oracle.jbo.server.EntityImpl.callCreate(EntityImpl.java:1350)
                at oracle.jbo.server.ViewRowStorage.create(ViewRowStorage.java:1134)
                at oracle.jbo.server.ViewRowImpl.create(ViewRowImpl.java:519)
                at oracle.jbo.server.ViewRowImpl.callCreate(ViewRowImpl.java:536)
                at oracle.jbo.server.ViewObjectImpl.createInstance(ViewObjectImpl.java:6113)
                at oracle.jbo.server.QueryCollection.createRowWithEntities(QueryCollection.java:2161)
                at oracle.jbo.server.ViewRowSetImpl.createRowWithEntities(ViewRowSetImpl.java:2774)
                at oracle.jbo.server.ViewRowSetImpl.doCreateAndInitRow(ViewRowSetImpl.java:2820)
                at oracle.jbo.server.ViewRowSetImpl.createRow(ViewRowSetImpl.java:2796)
                at oracle.jbo.server.ViewObjectImpl.createRow(ViewObjectImpl.java:11998)
...
                at weblogic.work.ExecuteThread.run(ExecuteThread.java:263)

The reason looks like since Department ID is not posted to the server side, so when new Employee record is getting created, it’s not able to set the foreign key properly.

                        Observation 2: Data is saved using Commit All button: can’t be reached
                       
                        Observation 3: Data is saved using Commit Master button: can’t be reached
                       
                        Observation 4: Data is saved using Commit Child button: can’t be reached

Case 2: Department ID field on the master table is configured with Auto Submit to true
Observation 1: In this case, the new Employee record is visible as expected just on single click:




                               Observation 2: Data is saved using Commit All button: both the records are saved. (Same as Case 1>Observation 2 )

                                Observation 3: Data is saved using Commit Master button: Only master table record is saved. (Same as Case 1>Observation 3 )

                                Observation 4: Data is saved using Commit Child button: Both the records are saved. (Same as Case 1>Observation 4 )

However, now when I try to delete a Department records by clicking the Delete Department button, I see the following exception:


To avoid this exception, we need to enable the Cascade Delete option on the Association, so now the association looks like this:



After making this change, if we click the Delete Department button and click any of the commit button, the records from both the parent and child tables are removed successfully.

Sample Application: MasterChildApp

No comments:

Post a Comment