Integrating Flashback in ADF Web Applications – providing the historic perspective

When we were doing the APEX vs. ADF session at the last day of the ODTUG Conference yesterday, Dimitri showed a nice feature in APEX that allows you to use Oracle Flashback Queries to look not just at table data as it is right now, but also as it used to be in the recent past. That inspired me to see how easy it would be to do something similar for an ADF application. As an example of what you can do, I will create a simple web page in this article that will show an editable table with Employee records and below that a read only table that can show the data as it is right now, or as it was at some specific time in the last. Through a dropdown list, the user can select from which point in time the data should be retrieved. It allows for quick inspection of changes in data and could serve as a basis for recovering data

The steps are:

• create a new JDeveloper application
• in the Model project, create Business Components from Tables for table EMP (SCOTT schema)
• in the View project, create a new JSF page; drag the EmpView data collection from the Data Control palette on top the page and drop it as an ADF table. Apply some refinements if you feel like them. Drag the Commit operation onto the page and drop it as a command button.
• run the page to confirm that you can see and edit data and save any changes you make.

Now for the more interesting bits:

Create the HistoricEmpsView ViewObject as a read only view object based on a flashback query that contains a bind variable. The FROM clause for this query is specified as:

FROM EMP as of timestamp systimestamp – :bind_flashbacktime/(24*60) Emp

This ViewObject will return Employees from a point in time as far back as bind_flashbacktime minutes.

Press Next and define the bind_flashbacktime bind variable.

Press Next and then Finish at your earliest convenience.

Go to the JSF page. Drag a PanelBox component onto the page. Add a panelHeader to it and set its text to something like Historic Perspective on Employees. Then drag the HistoricEmpsView1 collection on top of the panelHeader and drop it as a read only ADF Table.

Next, drag the ExecuteWithParams operation inside HistoricEmpsView1 collection to the panelHeader. Drop it as an ADF Parameter Form.

Change the label for the input text field to something like Data from how long ago? and run the page.

You will see something like this: the real, actual editable data on top and the historic data at the bottom. Initially they are the same, as the default value for the bind_flashbacktime variable is 0, meaning do not go back in time at all.

Now you can make a few changes in the editable block. Add 500 to the first four salaries for example and change some of the Job titles. Then press commit. You will see (the same data in the top block and bottom block):

And now for the interesting bit: change the value in the field ” Data from how long ago” from 0 to 5 – meaning: 5 minutes ago. Then press ExecuteWithParams. Now you will see this:

The top block still shows the actual ‘as is’  data. But the bottom part of the page now displays historical data. Using the Flashback Query it presents the data as it was 5 minutes ago.

We can make this slightly more user friendly:

1. Add a SelectOneChoice element to the page that will replace the InputText and Command Button:

              <af:selectOneChoice value="#{bindings['bind_flashbacktime'].inputValue}"
                                  autoSubmit="true"
                                  label="Data from how long ago?"
                                  id="timeSelector"
                                  valueChangeListener="#{MyBean.changeHistoryWindow}">
                <af:selectItem label="Actual" value="0"/>
                <af:selectItem label="1 Minute" value="1"/>
                <af:selectItem label="5 Minutes" value="5"/>
                <af:selectItem label="15 Minutes" value="15"/>
                <af:selectItem label="One Hour" value="60"/>
                <af:selectItem label="Two Hours" value="120"/>
                <af:selectItem label="One Day" value="1440"/>
                <af:selectItem label="One Week" value="10080"/>
                <af:validator binding="#{bindings['bind_flashbacktime'].validator}"/>
                <f:convertNumber groupingUsed="false"
                                 pattern="#{bindings['bind_flashbacktime'].format}"/>
              </af:selectOneChoice>

Note how this element inherits the value attribute from the inputText. And that it has an id as well as an autoSubmit attribute set to true. We have added a partialTriggers attribute to the table component, referring to this selectOneChoice, to have it updated automatically whenever the drop down is changed.

Also note how its valueChangeListener refers to a bean called MyBean. This bean is configured in faces-config.xml:

2. MyBean

<managed-bean>
<managed-bean-name>MyBean</managed-bean-name>
<managed-bean-class>view.MyBean</managed-bean-class>
<managed-bean-scope>request</managed-bean-scope>
</managed-bean>

and based on class MyBean that is implemented like this:

public class MyBean {
    public MyBean() {
    }

    public void changeHistoryWindow(ValueChangeEvent ve) {
        DCBindingContainer bindings =
            (DCBindingContainer)getExpressionValue("#{bindings}");
        FacesCtrlActionBinding queryHistoryView =
            (FacesCtrlActionBinding)bindings.findCtrlBinding("ExecuteWithParams");
        queryHistoryView.getParamsMap().put("bind_flashbacktime",
                                            ve.getNewValue());
        queryHistoryView.execute();
    }

    public Object getExpressionValue(String el) {
        ValueBinding vb =
            FacesContext.getCurrentInstance().getApplication().createValueBinding(el);
        return vb.getValue(FacesContext.getCurrentInstance());
    }
}

The page can now be run. We now have a drop down element where we can select the point in time to which we want to move our focus. Immediately after changing the value in the drop down, the table is refreshed and displays the data from the chosen ‘era’.