donderdag 8 december 2016

Finding jdbc/db connection leaks

Reason

Once I started creating my own application module instances, I got database connections not being released anymore.  And because we are all working in complex and big application, I didn't found the cause of the leak.
This blog will help you in finding the cause for the jdbc leak.
All my knowledge is based on the blog from Raul Castillo:JDBC Connection Leak

Finding that you have leaks

The first thing to do is finding whether you have leaks.  This can be done through the end-users stating that the application returns an error stating no connections are available anymore.
Now you can start investigating the cause of this issue.
You can use the Fusion Middleware console to investigate from which datasource the connections are not freed anymore.  It has a lot of helpfull statistics.
  1. Log in the Fusion Middleware Console : http(s)://<host>:<port>/em
  2. Go to your application on the left side under Farm and Application Deployments
  3. Click on the name of your application
  4. In the Application Deployment menu you can choose for Performance Summary.
  5. This will bring you to a new screen showing some default selected diagrams.  
  6. Click on the "Show Metric Palette" button to change the diagrams being showed.
    1. For this use case, you want to go to Related Targets > ServerName > Metrics > Datasource metrics > YourDataSourceName, domain level
    2. Here can select the following interessanting ones:
      1. Datasource - Available connections
      2. Datasource - Connection Leaks
      3. Datasource - Connection Pool size
      4. Datasource - Connections in use
These graphs are very handy when you want to see the evolution of the use of the connections of the datasource.  You can open multiple graphs from different datasource at the same time.  So  you can correctly define the datasource that is being leaking connections.

Once you have determined the datasource causing the leaks, you need to make sure WebLogic Server is cleaning them up after a while.  This can be configured in the WebLogic console:
  1. Login to WLS Console : http(s)://<host>:<port>/console
  2. Go to the datasources defined in the domain
  3. Select the datasource causing the leaks
  4. Go to Configuration > Connection Pool > Advanced 
  5. Make sure the parameter Inactive Connection Timeout is a positive number.  Put it to 5 for testing.  This parameter will cause WebLogic Server to cleanup stuck db connections  and causing a nice error message in the log files.
  6. You can find the logging in your <WLS managed server name>.log file.
  7. It is this error message that you need.  The Inactive Connection Timeout identifies after how many seconds he will try to cleanup the db connections.  When he finds db connections leaking, he will log a java stack trace indicating from where the leak is originating.  Here is an example:

    ####<Dec 8, 2016 8:12:07 AM CET> <Warning> <Common> <zcorinthe-1> <dev01-S1mc> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <1bf3554408c0f552:-3a194c43:158d428de6f:-8000-0000000000002b61> <1481181127371> <BEA-000620> <Forcibly releasing inactive resource "autoCommit=false,enabled=true,isXA=false,isJTS=false,vendorID=100,connUsed=false,doInit=false,'null',destroyed=true,poolname=MCPrintDev,appname=null,moduleName=null,connectTime=87,dirtyIsolationLevel=false,initialIsolationLevel=2,infected=false,lastSuccessfulConnectionUse=1481181100045,secondsToTrustAnIdlePoolConnection=10,currentUser=java.lang.Exception
      at weblogic.jdbc.common.internal.ConnectionEnv.setup(ConnectionEnv.java:366)
      at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:373)
      at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:339)
      at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:469)
      at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:363)
      at weblogic.jdbc.common.internal.ConnectionPoolManager.reserve(ConnectionPoolManager.java:125)
      at weblogic.jdbc.common.internal.ConnectionPoolManager.reserve(ConnectionPoolManager.java:158)
      at weblogic.jdbc.pool.Driver.connect(Driver.java:132)
      at weblogic.jdbc.jts.Driver.getNonTxConnection(Driver.java:658)
      at weblogic.jdbc.jts.Driver.connect(Driver.java:127)
      at weblogic.jdbc.common.internal.RmiDataSource.getConnectionInternal(RmiDataSource.java:548)
      at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:513)
      at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:506)
      at oracle.jbo.server.DBTransactionImpl.establishNewConnection(DBTransactionImpl.java:1056)
      at oracle.jbo.server.DBTransactionImpl.initTransaction(DBTransactionImpl.java:1237)
      at oracle.jbo.server.DBTransactionImpl.initTxn(DBTransactionImpl.java:6964)
      at oracle.jbo.server.DBTransactionImpl2.connectToDataSource(DBTransactionImpl2.java:304)
      at oracle.jbo.server.DBTransactionImpl2.connectToDataSource(DBTransactionImpl2.java:335)
      at oracle.jbo.common.ampool.DefaultConnectionStrategy.connect(DefaultConnectionStrategy.java:203)
      at oracle.jbo.server.ApplicationPoolMessageHandler.doPoolConnect(ApplicationPoolMessageHandler.java:620)
      at oracle.jbo.server.ApplicationPoolMessageHandler.doPoolMessage(ApplicationPoolMessageHandler.java:425)
      at oracle.jbo.server.ApplicationModuleImpl.doPoolMessage(ApplicationModuleImpl.java:9518)
      at oracle.jbo.common.ampool.ApplicationPoolImpl.sendPoolMessage(ApplicationPoolImpl.java:4529)
      at oracle.jbo.common.ampool.ApplicationPoolImpl.prepareApplicationModule(ApplicationPoolImpl.java:2459)
      at oracle.jbo.common.ampool.ApplicationPoolImpl.doCheckout(ApplicationPoolImpl.java:2269)
      at oracle.jbo.common.ampool.ApplicationPoolImpl.useApplicationModule(ApplicationPoolImpl.java:3168)
      at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(SessionCookieImpl.java:592)
      at oracle.jbo.http.HttpSessionCookieImpl.useApplicationModule(HttpSessionCookieImpl.java:224)
      at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(SessionCookieImpl.java:525)
      at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(SessionCookieImpl.java:520)
      at oracle.jbo.client.Configuration.getApplicationModule(Configuration.java:1609)
      at oracle.jbo.client.Configuration.createRootApplicationModule(Configuration.java:1514)
      at oracle.jbo.client.Configuration.createRootApplicationModule(Configuration.java:1485)
      at be.contribute.demo.adf.DocumentManagement.createAMInstance(DocumentManagement.java:127)
      at be.contribute.demo.adf.DocumentManagement.<init>(DocumentManagement.java:110)
      at be.contribute.demo.adf.DocumentManagement.<init>(DocumentManagement.java:67)
      at be.contribute.demo.adf.SimpleDocumentGeneration.<init>(SimpleDocumentGeneration.java:83)
      at be.contribute.demo.adf.bipublisher.SimpleEnvelopeDocumentGeneration.<init>(SimpleEnvelopeDocumentGeneration.java:38)
      at be.contribute.demo.adf.bipublisher.AcknowledgmentRecieptGeneration.<init>(AcknowledgmentRecieptGeneration.java:23)
      at be.contribute.demo.adf.bipublisher.ReportDataControl.generateReportMotives(ReportDataControl.java:503)
      at sun.reflect.GeneratedMethodAccessor1991.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

     at java.lang.reflect.Method.invoke(Method.java:606)
      at oracle.adf.model.binding.DCInvokeMethod.invokeMethod(DCInvokeMethod.java:690)
      at oracle.adf.model.binding.DCDataControl.invokeMethod(DCDataControl.java:2181)
  8. Now you look for your classes in the stack trace and make sure that for every creation of an am instance, you also close it correctly.
Hope this can help you in finding database leaks.

Have fun.

dinsdag 15 april 2014

ContributeGroep Sessions for OOW14 & JavaOne14

In the spirit of sharing the wonderful things our consultants do at customers, we entered a few of their efforts as proposals for Oracle Open World and JavaOne.

Here is the list of our entries:


ID Proposed Session Title Type Event
CON4630 Integration of Neo4j with Webcenter Content Conference Session OOW
Had enough of the linear searching into your documents?  Needing a dynamic search path in runtime?  Welcome to the world of graph databases.  Using a graph database to perform searches, allows you to add functionality from a totally different order.  Think about searches on relationships between documents or show recommendations of other users into your information model, just to name a few.  Basically, we add relationships between metadata, content, persons, applications, even if they didn’t exist before.  Graph databases allow for a dynamic search structure in runtime, providing more flexibility and agility to your portal.
CON3759 Securing web services - How to make a complex task look easy? Conference Session JavaOne
Not many developers like to implement or expose secured web services.  This is probably because securing web services is a complex matter.  Next to the complexity, the client/server architecture comes into play.  Both sides, client and server, will have their own implementation.  In an ideal world your backend code will not be impacted with the technologies in which it is build, nor which type of security you would choose. Since we are not living in a perfect world, those differences do have a big impact on your approach in bridging those 2 worlds. During this session we will explain how an enterprise service bus could help you out.  Not only in solving this predicament, also in adding clarity in your separation of concerns design.
CON3690 Single-click deployment in Application Express Conference Session OOW
Software development with Oracle Application Express is by no means affiliated with enterprise web development. Oracle itself positions APEX as a so-called rapid application development tool, but recommends other products as soon as projects get larger and more complex. I honestly believe, that APEX has the potential to cope with these large-scale projects. Needless to say, you need more than just plain APEX...

We have managed to incorporate a set of powerful tools in our day-to-day development process. These tools in combination with APEX allow teams to build high-quality database applications in a professional way. It takes no more than a single click on a button to fully deploy your application(s) to a target environment.
CON4662 Taskflows on Sale Conference Session OOW
Get ready for an alternative way of navigating between taskflows within ADF.  Instead of providing only the valid navigation route, we allow for a more flexible system avoiding dependency problems. This opens the way for a free form navigation keeping into account security and his history.  One of the possible implementations of this solution is the creation of a menu that is based on beans or configuration files. Introducing history to this system can overcome the use of the browser back button.
The pattern is already in use at a Belgian customer in providing web(ADF)-based solutions. This customer will be present to explain the business value and agility this brings to their application.

maandag 24 maart 2014

User Experience Event @AMIS

I had the pleasure to join the user experience event @AMIS in the Netherlands.  It was an event organised together with Oracle UX Team.
As a general impression: very well done.  Parking, event location, different themes and very good content are the main positive points that jumps into my mind.
Unfortunately were the sessions programmed without buffer time, so that when you followed one session you are certainly to be late for the next session in another room.  Luckily sessions were repeated, so I didn't have to miss anything and I was able to visit the different Oracle stands explaining the diversity of UX.

For a full list of the sessions, take a look at http://www.amis.nl/UxEvent/Lecture_details.html.
Here are some impressions I took from a couple of sessions:

UX Directions with HTML 5

This session was presented by a surprise act, in the sense that it was not Noel Portugal as presented on the site, instead it was a Belgian Oracle UX team member, namely Mark Vilrokx.  He showed us what they are working on.  It is very interesting to see how Oracle is investing into new technologies.  The things this guy showed us, was even new to our UX designer.  He focused on new possibilities with browser interactions in the mobile devices.  Knowing that a server can actually track your whereabouts, how bright it is in the room, whether you are using the application or not, is astonishing.  Not only passive information was gathered, also actions were triggered on the device.  Things like activating the vibrating functionality, playing a movie without user interaction.  This is a clear step forward for the browser in the war against native/hybrid applications.  Which made the off-line capability the only remaining drawback of the browser app in the mobile environment.

The architecture he used was a standard one: HTML5 -> Ajax calls -> REST Services.  To develop the mobile app, he used the Fuse Bootstrap, which is an adapted version of the Twitter Bootstrap.

Not presented in this session, but linked to the presenter is the Oracle Voice product.  It is a standalone product that will be released with the version 9 of HCM Cloud application.  It is an app that you can install on your phone or tablet, allowing you to speak to the application.  Not only in a question-response mode, where you answer to the question of the device, but also in entire sentences.
Example:  Create a new appointment Follow up meeting at Contribute next monday.  Not only will the solution interpret the sentence, it will also automatically fill in the fields of the new appointment.  For the missing information, extra questions will be asked.  When you have multiple opportunities for this account, a list of the possible opportunities for that account will be presented.
What is even more amazing is that the application does not need to learn.  You used to spend an hour saying/pronouncing bizarre and difficult words, this is not needed anymore.  Which indirectly means that you need to speak American English very well, preferably with a Californian accent :-).
A nice site note for my Belgian friends, this product uses Nuance for the Speech-to-Text and Text-to-Speech functionality.  Nuance bought the voice recognition software from our West-Vlaamse creatives L&H and is using their solution as one of the possible resolution software for the Speech-to-Text challenge.

Oracle's Strategy for Cloud User Experience

This session was presented by Jeremy Ashley, what a presenter.  It is not the content that struck me, instead his way of presenting and the feeling of clarity he gave us about this topic.   A must seen presenter!!
Back to the content.  The three main topics for Oracle UX are Simplicity, Mobility and Extensibility.
Simplicity is referring to the completeness of your solution.  It must be no too much, neither not too little, it must be exact for the job at hand.
Mobility is not about the mobile devices, instead it focusses on the way we work.  How we work differently now and in the future, then a couple of years ago.
The audience of these type of applications are people who will be using these not too often.  Hence the importance of keeping the distance between wanting to do something and doing it, must be a as small as possible.  The process that is the main driver, listens to the expression: Glass - Scan - Commit.  Resulting in an application giving the user an high level overview of the data, allowing him to drill down for more detail and then close his action.  This approach is very closely linked to the idea of 10-90-90, meaning that 10% of the tasks are performed by 90% of the users in 90% of the time.  These 10% are the main focus of these applications.  For full feature functionality, the user will be brought to the actual application.
Based on their experience with the Oracle Cloud solutions and fusion applications, the UX team delivers a set of patterns for you to use.  These can be found at http://www.oracle.com/ux.
While most of the development and testing is done through HTML5 and then pushed to the ADF team, APEX is gaining momentum here.  In the "near" future, the APEX team will also include these patterns, just like ADF does.

UX Today with ADF

A presentation done by Sten Vesterli.  This session was really focused on ADF, like the title mention.  From the first moment it was clear that Sten was a guy with a lot of experience.  Here are some of his main points during the session:
From http://www.vesterli.com/2014/03/20/good-user-experience-at-low-cost/
  • A clear graph on the different types of applications you can build:
  • Use GUI mockup tools to create samples of your UI which don't have a high fidelity.  Reason: possible no feedback from end-users due to the hard work already done AND the possible feedback you get is that it could be finished tomorrow.  Conclusion: make sketches.  Possible tool: Balsamiq
  •  Another great point made by the presenter is the difference in type of application you can build between Forms,Apex and ADF.  While Forms and Apex applications have a 1-1 relationship with the underlying data model, ADF doesn't.  With ADF you have more a n-n relationship, allowing for a far greater UI experience.  Conclusion here: do not generate your UI screens.

Don't generate your user interface, design IT!

Session given by Lonneke Dikmans from Vennster, ACE Director in SOA & BPM.
The problem according to Lonneke is:
  • Using BPEL/BPM Human Tasks
    • too fine grained
    • too much data in process
      • need to adapt the process to changes in the UI
      • inflexible for use -> ex. no bulk operations possible
    • not enough data for the UI -> only task data is available
The solution for this is to take an alternative approach:
  • separate both solutions => BPEL/BPM and UX
  • take the following actions in your project
    • Define the different personas.  Who will be using the application, how often, from which type of devices, do they have knowledge of the app, ...
    • Define different scenario's for different type of personas
    • Design the interaction process
  • Ex: while the HR persons define the process, it should be the users themselves who define the UX/App
My question for Lonneke: how should we build BPM applications then?  From the BPM flow or build a separate application that calls on the BPM API's?
Answer from Lonneke: take the latter one.  This way the UI is really build for the end user and you have more flexibility.

Leveraging FMW for UX

Another session from Lonneke Dikmans.  This session was a simple overview of the main components of the FMW stack of Oracle.  Some points worth remembering:
  • How to test the experience:
    • Do Usability testing
    • Do SCA testing => use mock responses
    • Do Load testing
  • Tip: learning by doing : learn whether something works or not
    • ex: google class: 1st version done in 2 days
  • Monitoring

Oracle and Mobile: From Design to Device; The tools that make it happen

Session by Luc Bors.  Simple overview of ADF Mobile.
There seems to be a misunderstanding in the comparison between ADF mobile or hybrid solutions and native solutions.  According to Luc and Oracle(Oracle is saying this also, I know I did it also before), the effort in making a native app is a lot bigger and more difficult then making a hybrid solution for multiple platforms.  It is true that you need to make an app for each platform and that reuse at the level of the mobile app is almost zero.  Nevertheless, making these mobile apps is only a small part of the total project.  The major effort resides in building the back-end services, including security.
Take into account that the hybrid solution from Oracle only supports 2 platforms, while there are 4 in the market.  

According to me is Oracle ADF great at the data oriented apps, while building great looking apps are better done in native solutions.  One of our main references in this area is the Royal Belgian Football Association, who are really looking at their target audience and decide on the technology uses, like Oracle Forms, ADF, ADF Mobile and native mobile applications.

Conclusion

A great event with may thanks to AMIS and Oracle for organising this.  All speakers at this event where ACE Directors or Oracle employees, pushing the available knowledge at this event at a great height.  Looking forward for a next event like this.
At the following URL you can have a look at the presentations themselves: http://technology.amis.nl/2014/03/24/sharing-the-slides-of-the-ux-event-presentations/

vrijdag 3 januari 2014

ADF: Putting IE in a certain document mode

Problem

In our ADF application we used some javascript to put the focus on the first field of an af:query component.  This seems to work great when running directly from JDeveloper, but doesn't if the application is deployed on a separate WLS.

Solution

First we thought it is the javascript itself, but this was not possible since we do not change the code.
One of our JSF experts, Rudy De Busscher, figured out that the compatibility mode of IE changed between both deployments.  How or why, remains still a mystery.

So, now we need to force IE to keep his IE8 compatibility mode.  To do this, you need to put a meta tag in the header of your page.  This is easy enough to do in ADF, in the af:document tag, add a facet with this tag in, like this:

<af:document id="d1" title="Test Javascript">
   <f:facet name="metaContainer">
      <f:verbatim>
         <meta http-equiv="x-ua-compatible" content="IE=8"/>
      </f:verbatim>
   </f:facet>
....

The problem with this solution is that the meta-tag will be put after your stylesheet tags, which will result in the fact that this meta-tag is not taken into account.
To solve this problem, you need to perform a small operation.  Since this seems to be a hack, I'm not sure it will work in all ADF versions.  I tested it with ADF 11.1.1.3.0.
Make your code look like the following:

<f:view>
   <f:verbatim>
      <head><meta http-equiv=x-uq-compatible" content="IE=8"/></head>
   </f:verbatim>
   <af:document id="d1" title="Test Javascript">
   ...
To check whether IE is taken this into account, use the F12 key to open the Developer Tools, in the top menu you will find the Document Mode.  This will indicate in which mode you are operating.  Also have a look at the header of the document, the meta-tag of http-equiv should be after the title, but proceeding the stylesheet tags.

Have Fun.

F.

donderdag 2 januari 2014

ADF BC: Find out what is really happening in the database

Problem

While there are multiple ways (see previous blog) within ADF BC to see what is being generated and executed in the database, perhaps you are not satisfied with the information provided nor the overhead of the other loggings which are blocking your clear view on the matter.
Or perhaps you are missing information like how many times a query is executed, how long it took, how many fetches were used, ... 

Solution

Use the logging of the database to find the required information.  You can enable your sessions or even the entire database to activate logging that can be used to give you a clear overview on what is being asked from the database.
To activate this logging you need to perform the following actions:
  1. Activate the logging in your session
    Execute the following statement : alter session set sql_trace=true
    You can execute this command in your application module impl class by overriding the afterConnect() method.  I'm also using this method to set some optimizer hints for my queries, like optimizer_index_cost_adj and optimizer_index_caching.
    Example:
    • @Override
      protected void afterConnect() {
         super.afterConnect();
         executeCommand("alter session set sql_trace=true");
      }
  2. Perform the necessary actions in your application for which you want to have the trace.
  3. You can now stop the trace by executing the following statement: alter session set sql_trace=false
    or just perform action 4.
  4. At this point the database will have generated a very detailed, not easy to read and understand trace file.  This file can be found under the user dump directory, which is defined by the database parameter user_dump_dest (show parameter user). 
  5. Now we need to parse this file to a more readable file by executing the following statement:
    tkprof <sid>_ora_<pidid>.trc <result_file> sort=fchela
    Since it is very hard to know what process id you need, I just always took the last one :-)
    I added the sorting criteria to see the statements first with the biggest elapsed time.  Information on all parameters for tkprof can be found here.
    Example : tkprof orcl_ora_2463.trc test.txt sort=fchela 
  6. Now you have a file that is readable, namely test.txt.  This file will give an overview of all statements being executed by your sessions and at the end an overview.  Let's have a look at output for 1 statement.
  7. Only take the statements into account where the parsing user is not SYS.  The latter is used for database internal queries and do not adhere to the same tuning rules as your custom queries.
Here is an example output for a very simple query:
Let's have a look at the important bits of information:
  • As 3th part of information you see the query like it has been asked at the db.  Here you see whether correct bind variables are used.  The only drawback of this technique is that this query is not linked directly to a certain view object.  It is up to you to determine which view object has generated this query.
  • The count column of the Parse row, should be 1 or as low as possible.  This indicates how many times the db needed to parse this particular statement.  If you are not using bind variables, you will see that for each value a new statement overview is given and that the parse count will still be 1.
  • The count column of the Execute row, should represent the number of times this query should be executed.  If you would have expected a lower number then the one appearing here, then BC has executed this query more often.  To solve this problem, you will need to look at the page definition files to determine when and how often the query is executed or even in your custom code when you asked to execute query on an iterator.
  • Normally the cpu time of the execute row, should be very low.  If this is not the case, then it indicates that the query itself is very complex and has a lot of hierarchies of tables.  Or it could be because it is a DML-statement.
  • Now comes the most important row, the Fetch-row.  The fetch count number indicates how many times the application has fetched rows for this query.  If the execute count is 1 and the fetch count is 10, this means that ADF BC has executed the query once, but needed to go 10 times to the db to get all data.  To resolve this problem, just change the 'in Batches of'-tuning parameter of your view object.  Look at the following blog for some tuning guides.
These were for me the most important pieces of information to be able to tune my ADF application.

Have Fun.

F

ADF BC: Adapting the where-clause of a view criteria

Problem

You have build a working ADF BC application.  After some tuning tests, it seems that your application can use some tuning.  So you, or someone above you, decides to call an expert DBA or SQL tuning specialist.

Result : you need to change the way the where-clauses are created.
If you are lucky, it is just a where-clause you have written.
If you are less lucky, it is a where-clause created by a view criteria, in this case you can not change it.  You can only activate or deactivate some properties.

In our case this wasn't enough.  The ADF BC framework generated clauses like
UPPER(LAST_NAME) LIKE UPPER(:LastName_bvar || '%')


Solution

The first thing you need to do is to remove the upper-statement.  This can easily been done by deselecting the 'Ignore Case' property in the edit view criteria screen.

So now you are getting the following clause
LAST_NAME LIKE (:LastName_bvar || '%')

While this seems fine for you, your tuning specialist still isn't happy.  He wants to get rid of the wildcard.

To accomplish this requirement you will need to get more creative.
Add the following code to your ViewImpl class or your base ViewImpl class:
    /**
     * Source : http://tompeez.wordpress.com/2011/08/21/extending-viewcriteria-to-use-sql-contains-4/
     * Adapting the creation of the where-clause to remove the '%' after the bind variables of the like statements
     * At the same time we add the '%' wildcart at the end of the variable value.
     * We will do this for all bind variables with custom property LIKE_CLAUSE=CUSTOM
     * This method gets called for all bind variables
     * @param viewCriteriaItem
     * @return
     * @author Filip Huysmans
     */
    @Override
    public String getCriteriaItemClause(ViewCriteriaItem viewCriteriaItem) {
        ArrayList<ViewCriteriaItemValue> lArrayList = viewCriteriaItem.getValues();
        if (lArrayList != null) {
            ViewCriteriaItemValue itemValue = (ViewCriteriaItemValue)lArrayList.get(0);
            if (itemValue.getIsBindVar()) {
                Variable lBindVariable = itemValue.getBindVariable();
                // check for the special LIKE_CLAUSE in the used bind variable
                Object obj2 = lBindVariable.getProperty("LIKE_CLAUSE");
                String likeClause = (obj2 != null ? obj2.toString() : "null");
                if (likeClause != null && !likeClause.isEmpty() &&
                    !"null".equals(likeClause)) {
                    if (viewCriteriaItem.getViewCriteria().getRootViewCriteria().isCriteriaForQuery()) {
                        // normal query execution
                        return getLikeClauseForDatabaseUse(viewCriteriaItem,
                                                           likeClause);
                    } else {
                        // for in memory we don't need to anything so just return '1=1'
                        return "1=1";
                    }
                } else {
                    // no special treatment for all other CriteriaItems
                    return super.getCriteriaItemClause(viewCriteriaItem);
                }
            }
        }
        // fallback call
        return super.getCriteriaItemClause(viewCriteriaItem);
    }
 
    protected String getLikeClauseForDatabaseUse(ViewCriteriaItem aVCI,
                                                 String typeLikeClause) {
        ArrayList<ViewCriteriaItemValue> lArrayList = aVCI.getValues();
        ViewCriteriaItemValue itemValue = (ViewCriteriaItemValue)lArrayList.get(0);
        String whereClause = "1=1";
        if (itemValue.getIsBindVar()) {
            Variable lBindVariable = itemValue.getBindVariable();
            Object objVarVal = ensureVariableManager().getVariableValue(lBindVariable.getName());
            String varVal = null;
            if (objVarVal != null) {
                // Adding the wildcard to the bind variable and putting the bind variable in upper-case
                varVal = (objVarVal.toString().toUpperCase() + "%");
                ensureVariableManager().setVariableValue(lBindVariable,
                                                         varVal);
            } else {
                // No value specified => return no where clause
                return null;
            }
 
            String bindVarName = lBindVariable.getName();
            if ("UPPER".equals(typeLikeClause))
                whereClause =
                        "UPPER(" + aVCI.getColumnName() + ") like :" + bindVarName +
                        " ";
            if ("CUSTOM".equals(typeLikeClause))
                whereClause =
                        aVCI.getColumnName() + " like :" + bindVarName + " ";
        }
        return whereClause;
    }
As mentioned in the java doc of this code block, you need to add a custom property to the bind variables for which you want this to take effect. Add a custom property with the name 'LIKE_CLAUSE' and a value of 'UPPER' or 'CUSTOM'.

From this situation you can add any functionality you want to make the where-clause exact what your tuning specialist is looking for.

Have Fun.

F

maandag 16 december 2013

ADF: annoying warnings

Fact

In the logging of your application server, you see often the following warning :

<SimpleSelectOneRenderer><_getSelectedIndex> Could not find selected item matching value "0" in RichSelectOneChoice[UIXEditableFacesBeanImpl, id=value70]

Problem

You are probably using a component which generates fields dynamically, like the query-component.  If you have defined a LOV for one of the fields this component needs to show and you have specified that it needs a "No Selection" item in the UI Hints of the LOV, then this warning will popup.

The warning you are getting is just saying that he tries to map a selected value "0" to the list he recieves, in which case he did not find it.  The id is pointing to the field in the query component in this case.  The fields are numberd from value00, value10, value20 to valueXX.

Solution

The solution is to remove the selection of the "No Selection"-item, but this will probably add another problem to your list.  To solve this problem you can do the following depending on the type of the view-component of your LOV.

  • View based on static values.
    In this case just add an empty row and put it on top.  The order for these kind of views is determined by the order in the list.

  • View based on a query.
    In this case just add an union-clause and add an empty row through the dual-table.  Also add an order by-clause to put the null-row first.