LOV from Shared Application Modules with a different data source - table or view not found

In many cases lookups are being used to create a readable image of a key column inside a database table. Many of these lookups are globally defined for many applications inside the company. So in general it is a good idea to generate a base package / project containing business components for these lookup references.


A sampe project setup could look something like this.

In this case, we used one project for two application modules, the SharedModule stating the (session) shared application module from the base packages. This allows to use the same view object instances across a user session:

It references the oracle schema Salary with the table SALARY_STEPS (table def can be found in the database project, just import it to a different schema than HR).

The main data ApplicationModule HRService connects to the oracle default HR schema. Inside the ViewObject EmployeesView and a list of values is defined on the attribute Salary using the ViewObject instance from shared am instance:


When starting the HRService Application Module with the AM-Tester (right-click -> run), the simple data model is shown.


Double clicking the EmployeesView Instance we will receive a ORA exception that the SALARY_STEPS table is not found and we see, that the lov of the Salary attribute is empty


The main issue is, that the Application Module SharedModule uses the same connection as the HRService, since (as a session shared application module) it is always nested inside the Root Application Module of the View Object that uses a view object instance as a view accessor from the shared AM. So per definition of session shared application modules, it is not possible declaratively to use a different data source to the main data source.


If we go back to the Model.jpx and change the type of the shared application module from session scoped to application scoped, the behaviour of the shared module changes drastically.

Each shared application module in this scope will provide a separate singleton root application module, so one database connection will be opened for each shared application module. But this feature is necessary for declarative lov definition on shared application modules. So running the HRService with this settings, we see that the LOV is correctly defined and we can use it as any other LOV View Accessor

So even if you do only use one data source, it might come in handy to use a shared application module view object instance for your lookups, especially when encountering large result sets for the lov, which could be stored in memory for each user session or even across the whole application.

If you want to give it a try, head over to the new German ADFCommunity github [https://github.com/ADFCommunityDE/SharedAMMultiDataSource.git] and pull the sources :)

By the way, this Application was created using JDeveloper 12.2.1 but the topic is useable for 11g R1 and R2 as well!