Oracle ADF - Debug view object query with parameters

Introduction

Slow and huge non optimized select queries are one of the main problem when developing ADF application.
To track SQL performance (and other) issues, we need to be able to see select queries that we send to database.

Solution

View object performance issues are mainly connected with execution of executeQueryForCollection method that fill
VO with rows from database.

If you want to track all VO queries that return collections you need to overwrite executeQueryForCollection in view
object implementation. The best practices is to have one global ViewObjectImpl that is at the to of the View Object hierarchy (that will all View Object extend).

So to see queries, you need to write following in VO implementation class:

public void executeQueryForCollection(Object rowset, Object[] params,
                                          int noUserParams) {        
        System.out.println("VO name: " + this.getFullName());
        System.out.println("Query is: " + this.getQuery());
        super.executeQueryForCollection(rowset, params, noUserParams);        
    }

You can freely use any debug tool you like (Log4J for example), I just use java's System.out for convenient reason.

If we try to test this method we will see that ADF write only parameters names (:vc_temp1, :vc_temp2 or something like this)
and parameters values are missing.

To see values, wee need to read second parameters of executeQueryForCollection method. This parameter is filled by framework
and it contain pairs (name, value) of parameters. It is kind of strange (to use array of object of array of objects -- Object[][])
for parameters saving if you ask me, but there must be a reason. :) We will read it with following method:

private void getParametersValues(Object[] params) {
      if (getBindingStyle() == SQLBuilder.BINDING_STYLE_ORACLE_NAME) {
        if (params != null) {
          for (Object param : params) {
            Object[] value = (Object[])param;
            System.out.println("Name=" + value[0] + "; Value=" + value[1]);
          }
        }
      }      
    }

You just need to insert this method into executeQueryForCollection and you have full query monitoring from you JDeveloper console:

public void executeQueryForCollection(Object rowset, Object[] params,
                                          int noUserParams) {        
        System.out.println("VO name: " + this.getFullName());
        System.out.println("Query is: " + this.getQuery());
        getParametersValues(params);
        super.executeQueryForCollection(rowset, params, noUserParams);        
    }