Oracle ADF and Virtual Private Database

As you probably know, Oracle ADF is a complete JEE application development framework from Oracle. It is targetted at companies that already have an Oracle Database and perhaps legacy applications developed with Oracle’s “Forms” technology. The client I’m working for at the moment is such a company, and we’re developing an application in ADF that is going to replace (a part of) their Forms application eventually.

One would expect a framework designed by Oracle for use with an Oracle database to have excellent support for specific features of the Oracle database. For a lot of features this is true, but at my client’s, we were unlucky to have chosen to use a database feature that is not supported that well in ADF. That is: “out of the box”. With a lot of “trial and error” and some help from some experts at the forum on Oracle’s Technology Network, I managed to get it working. Read on to find out what problems I encountered and how I solved them. (Basic knowledge of ADF is assumed in the rest of this article.)

The problem

The problem we were facing, was the following. In our database, nearly all data is valid only in a specific year. But historical data from previous years has to be kept in the database and has to be available in the user interface. Especially near the year change, users will be switching between the new year and the old year frequently.

The Oracle database has a feature called Virtual Private Database (VPD). The raison d’être of this feature is security-related. It is mostly used to set a user ID once, and be sure that only data is returned from the database that that user is allowed to see and/or edit. We figured we could as well use this to filter the data to only get results that are valid in the selected year.

At the database side this works pretty well. There is a PL/SQL function we can call to set the context for the VPD. The PL/SQL call to do this, looks as follows:

Calling this function from an ADF Application Module is pretty standard in ADF:

In this snippet, SETCONTEXT is a String constant containing the previously shown PL/SQL call. The variables user and year contain exactly what you would expect based on their names. Of course this code can throw exceptions, but I left out the exception handling here for brevity.

So on the database side everything is in place and we know how to call the right function. But now the difficulties start…

Sessions

In an ADF application, both the Model and the ViewController part maintain a session for a logged in user. At the ViewController side, this session is directly linked to the user’s HTTP session to the web server. At the Model side, this session is linked to the Application Module. One of the problems is that Application Modules are pooled, much like database connections are pooled in a JDBC Connection Pool in an ordinary EJB application. So there’s no guarantee that you are using the same Application Module object during a single session. So at the Model side of our project, we cannot store the selected year in a member variable of the Application Module. Instead, we have to store in on the Session of the Application Module. This oracle.jbo.Session object has a userData HashTable that we can use to store the selected year. So we implemented a setYear() method in our custom ApplicationModule implementation:

This simply puts the selected year on the userData Map. (YEAR is a String serving as the Key in the Map.) Note that the setContext() method only gets invoked as the year is really changed. This setContext() method then calls the PL/SQL function in the database, using the approach outlined above.

Of course, the selected year is also stored on the session at the ViewController side too. Now the next challenge is to make sure both are in sync. Since we never know if we have the same ApplicationModule object, the most practical solution seemed to set the right value at the start of every request. So we implemented a PhaseListener:

Of course, this listener has to be registered in the faces-config.xml file:

Now at the start of every JSF cycle, the selected year gets set in the ApplicationModule.

Nested application modules

Perhaps you would think we are already there. But there is yet another challenge. As Oracle advises, we separated our application into different modules and created different ApplicationModules that are all nested into a single Root ApplicationModule. Now that Root AppModule is the only one that maintains a Session. You might have thought why I did not show the implementation of getYear() along with the setYear() method above. That’s because the getYear() is a bit more complicated due to this nesting of application modules:

Refresh the view

Ok. So now we’re finished? Not yet! One more thing to do: we have to make sure the view gets updated every time the user selects another year. As we want to show the year selector on every page in our application, we have incorporated it in our page template. That means we don’t know in advance what data sources we have to update after changing the year. First let’s see how we added the year selector to our page template:

Note that autoSubmit is set to true, so the page is submitted as the user changes the year. The disabled value makes sure the user cannot change the year if there are uncommitted changes pending. The valueChangeListener setting causes the yearValueChanged() method to be called upon a year change. We implemented that method as follows:

Note how we iterate over all IteratorBindings and call executeQuery() on all of them to make sure all data is refreshed after a year change.

That’s it!

Well, almost. There’s one more thing I’m not really sure of (yet). In section 39.7 of their Fusion Middleware Developer’s Guide, Oracle shows a (in my opinion rather hacky) way to make sure Session information is saved between different incarnations of the ApplicationModule. I’m not sure if this is needed in our approach, since we set the year at the start of each JSF lifecycle anyway. But I implemented it, “just in case”, it doesn’t harm anyone.

Well that was pretty complicated. Especially since I had to find bits and pieces of information in the Oracle docs and on the web. Luckily I got some help on the OTN forum. I hope others can use this information to solve similar problems in their applications. Please let me know if this post was of any help to you or if something is not clear!

2 thoughts on “Oracle ADF and Virtual Private Database

  1. could be that this is simply a less than suitable use of VPD?

    maybe "We figured we could as well use this to filter the data to only get results that are valid in the selected year." is the reason that its complicated?

  2. The simple elegent solution is just to override prepareSession method in ApplicationModuleImpl class

    @Override
    protected void prepareSession(Session session) {
    super.prepareSession(session);
    //Merih solve it elegent….
    String appContext = "Begin besportal.SetUserP(‘MILGOR’); END;";
    java.sql.CallableStatement st= null;
    try
    {
    st = getDBTransaction().createCallableStatement(appContext,0);
    st.execute();
    }
    catch (java.sql.SQLException s)
    {
    throw new oracle.jbo.JboException(s);
    }
    finally
    {
    try
    {
    if (st!= null)
    {
    st.close();
    }
    }
    catch (java.sql.SQLException s2){}

    }
    }

    Merih ilgör.

Comments are closed.