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.

Preparation:

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:

Issue:

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



Analysis:

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.

Solution:

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!

Cheers!

Handling very slow execution of CreateInsert in JDeveloper 12.2.1

Many of you might encounter a serious performance issue with the createInsert functionality of ADF in 12.2.1. In detail:

If you have a large result set after a query (ex. a master data dialog) and you want to create a new record, the framework executes the findByKey method as many as four times  as there are records in the result set of the query (check Andrejus Baranovski's blog entry: Evil behind the ChangeEventPolicy PPR). This only happens, if you did not fetch all data up to the point of inserting a new row (so having -1 as range size will fix this, although querying many thousands of rows to create one seems not to be a good idea ;) ).

As we faced the same issue for a customer project, we tried to find out, what exactly is the issue here.

Preparation:

In our test we have the following conditions - A Table containing  ~ 70000 entries with randomly generated Char content in each column. For this, we created the default BC, so an Entity Object with the default settings, a corresponding default View Object and an Application Module.

On View Controller side, we have just a plain page with an af:table created by drag and drop from data controls. On the table, we set the following properties:

contentDelivery="immediate"
autoHeightRows="10"
rangeSize=13 -> leads to property change of iterator in page definition

For completion of the preparation, we add the navigation and crud buttons to the ui.

When running the page and clicking on CreateInsert, we see, that this takes a long time (it can get significantly worse for real data).

On a second run (make sure a new session is built up), click last first and then the create insert. This takes some time for the last-button click, but the insert afterwards is done immediately. So what happens?

Problem description:

The newest version of ADF has some changes in the Key mechanism. The most important (in general but also in our case) is that a key of a newly created  row is not null itself, but a Key Object containing null values, which seems like a good idea in most cases.

This change has a serious impact on the retrieveByKey in the ViewObject method, which seems to check if(key == null), which is never true in the new Version (since we always have a Key object at hand).

In case the data is not already fully fetched from the database, ADF creates a new RecordSet for the find by key method (which makes sense in case you are searching for a row that might not be already in the fetched Rows). In general this new View Object uses the constraints of the original View Object, but in most cases for master data dialogs, there are no real constraints in the first place.

In case of the insert, a findByKey is executed (coming from the af:table, which is refreshed by ppr; again, see the post of Andrejus), coming with a key that is not null, but contains only null values. Since the fetch size is obviously not reached (we have 13 rows out of 70000), a new View Object is created in background and executed for the findByKey, which never will be finding any Row. Strangely, this seems to be executed for each row that is in the range and before the currently inserted row. So in worst case, the query would be executed up to 12 times in the example.

Solution:

Personally, I think, Oracle must take a look at this and maybe change some implementations at this part. But as we know, it might take a while until fixing the issue. So here is a workaround. In your ViewObjectImpl (better, a base ViewObjectImpl that is superclass of all your View Objects) override the following method:

   @Override
   protected Row[] retrieveByKey(ViewRowSetImpl rs, String keyName, Key key, int  maxNumOfRows, boolean skipWhere) {
        if (!key.isNull() || keyName != null) {
            return super.retrieveByKey(rs, keyName, key, maxNumOfRows,    skipWhere);
        }
        return new Row[0];
    }
 
This is just a safety fallback, that if all of the key columns are null the findByKey mechanism is not executed at all. In all other cases (i.e. you really want to search a row by its key or part of its key), the framework default will do its job.

In the Example, we created the same UI with a View Object that has the fix (FixedRandomEntriesView). In this case, the create is done immediately, even if the fetch does not have all rows, wich solves our problem.

One should state, that this solution is only applicable for blank inserts (i.e. no DBSequences etc.). In this case, you have to put some further effort to the overriden method to check if you are in insert mode or search mode.



You can download the sample app here: CreateInsert12211 Example
To work with our example data, you can use this sql script to create the table and insert data: Random_Entries.sql

SelectOneChoice im JDeveloper 12.2.1

Im JDeveloper 12.2.1 hat sich ein Stylingingfehler bei der Komponente SelectOneChoice eingeschlichen.

In einem Formular rutschen die Inhalte nach oben, sowohl im Read-Only als auch im Auswahl-Fall. Im Rahmen einer Tabelle fällt das Verhalten nicht auf, da dort der Inhalt zentriert wird.

Hier die Beispiele (Felder JobId, ManagerId und BepartmentId):

Um den Fehler zu korrigieren müssen wir den Skin anpassen.

Dazu erstellen wir einen Skin:

Im ViewControler-Projekt New > From Gallery wählen

 "ADF Skin" wählen:

 Wir wählen einen schönen Namen:

und wechseln in den Source-View des Skins und fügen wir folgende Zeilen hinzu:

af|selectOneChoice .AFPanelFormLayoutContentCell  {

    padding-top : 8px;

}

af|selectOneChoice af|panelFormLayout::label-cell {

    padding-top: 8px;

}

und erhalten:

Damit die Anzeige klappt, einmal die Applikation undeployen und neu starten und schon erhalten wir:

TEAM After Work Knowledge - JasperReports und PL/SQL

Haben Sie sich schon immer die Frage gestellt: Wohin geht die Reise nach Oracle Reports? Wir auch - und haben uns in diversen Fallstudien mit verschiedenen Anbietern von Berichterstellungssoftware auseinandergesetzt.

Da wir wissen, dass nicht jeder die Zeit aufbringen kann, sich tagelang in alle Frameworks einzuarbeiten, haben wir bei TEAM eine neue Veranstaltungsreihe konzipiert. Das TEAM After Work Knowledge - kurz TAKE.

Ziel der Veranstaltungsreihe ist es, praktisches Wissen rund um Oracle Datenbanken und den damit verbundenen Themen Personen mit Entwicklerhintergrund zu vermitteln und das gelernte auch direkt anzuwenden.

Als Start in die Reihe wollen wir das oben beschriebene brisante Thema aufgreifen und den Workshop JasperReports und PL/SQL am 17.03.2016 bei TEAM in Paderborn durchführen. Man kann an den JasperReports Tutorials aus dem Internet schnell sehen, dass reine SQL Reports ohne große aufwände einfach erstellt werden können. Wenn man aber Berichte erstellt hat, bei denen Informationen über PL/SQL aus der Datenbank abgefragt werden, wird es mit den Basics ungleich komplexer.

An diesem Abend werden wir nach einer kurzen Einführung in JasperReports einen Weg aufzeigen, wie effektiv und einfach PL/SQL in JasperReports eingebunden werden kann.

Anschließend haben Sie die Möglichkeit, an Ihrem eigenen mitgebrachen Laptop im Rahmen einer Übung das Gelernte zu vertiefen. Natürlich wird zur geistigen Stärkung auch für das leibliche Wohl gesorgt.

Sollte Ihr Interesse geweckt sein:

TAKE "JasperReports und PL/SQL"
am Donnerstag, den 17. März 2016 bei TEAM
Beginn: 17:00 Uhr | Ende: 19:30 Uhr.
Die ausführliche Agenda und die Möglichkeit zur direkten Anmeldung finden Sie hier.

Wir freuen uns sehr auf ihr Kommen zu TAKE!