Oracle ojdbc drivers not logging SQL query parameters

Question

I've configured my application properly to log SQL queries but the parameter values are not shown but question mark instead (?).

I followed a lot of questions in StackOverflow as well as official documentation but nothing worked.

My application runs on OpenLiberty server.

Driver: ojdbc6_g.jar

My configuration:

com.ibm.ws.logging.trace.specification=*=audit:oracle=CONFIG
oracle.jdbc.Trace=true

# set levels 
.level=SEVERE
oracle.level=INFO
oracle.jdbc.driver.level=CONFIG
oracle.sql.level=CONFIG
# configure handlers
oracle.handlers=java.util.logging.ConsoleHandler
java.util.logging.ConsoleHandler.level=CONFIG
java.util.logging.ConsoleHandler.formatter=java.util.logging.SimpleFormatter

Is there any way to see the SQL parameters whitout using third party libraries nor modifying application's code?

Answer 1

Is there any way to see the SQL parameters whitout using third party libraries nor modifying application's code?

You have to modify your logging.properties with oracle.jdbc.driver=FINE as that is the exact logger name that records the parameters.

I've configured my application properly to log SQL queries but the parameter values are not shown but question mark instead (?).

The Oracle driver was coded to log the parameters as individual log statements. From your question it appears the problem you are trying to solve is a formatting issue caused by the Oracle driver creating multiple log records for the parameters and one for the query itself which just contains the binds. Since you have a formatting issue then the logging framework requires you to create a custom formatter to convert the log records to a specific output format you would like to see.

The only thing novel about the formatter you have to create is that you need to coalesce multiple log records into one nicely formatted message.

Here is an example to get you started:

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.util.ArrayDeque;
import java.util.logging.Formatter;
import java.util.logging.LogRecord;
import java.util.logging.SimpleFormatter;

public class CoalesceFormatter extends Formatter {

    private final ArrayDeque<LogRecord> records = new ArrayDeque<>();
    
    private final Formatter target = new SimpleFormatter();
    
    @Override
    public synchronized String format(LogRecord record) {
        if (records.isEmpty()) {
            if (!isStart(record)) {
                return target.format(record);
            } else {
                records.add(record);
                return ""; //suppress output
            }
        }
        
        if (isEnd(record)) {
            LogRecord first = records.poll();
            String query = this.formatMessage(first);
            for (LogRecord parameter;  (parameter = records.poll()) != null;) {
                query = query.replaceFirst("\\?", this.formatMessage(parameter));
            }
            
            LogRecord clone = clone(first);
            clone.setMessage(query);
            clone.setParameters(null);
            assert records.isEmpty() : records;
            return this.target.format(clone);
        }
        
        if (isDelayed(record)) {
            records.add(record);
            return ""; //suppress output
        }
        
        //Normal record
        return target.format(record);
    }
    
    private boolean isStart(LogRecord r) {
        //TODO: this is the call site of preparing the query. 
        return "prepareStatement".equals(r.getSourceMethodName());
    }
    
    private boolean isDelayed(LogRecord r) {
        //TODO: this needs more filtering
        return "setParameter".equals(r.getSourceMethodName());
    }
    
    private boolean isEnd(LogRecord r) {
        //TODO: fix as needed
        //valueOf deals with null
        return String.valueOf(r.getSourceMethodName()).startsWith("execute");
    }
    
    private LogRecord clone(LogRecord r) {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try (ObjectOutputStream oos = new ObjectOutputStream(out)) {
            oos.writeObject(r);
        } catch(IOException ignore) {
        }
        
        
        try (ByteArrayInputStream in = new ByteArrayInputStream(out.toByteArray());
                ObjectInputStream oos = new ObjectInputStream(in)) {
            return (LogRecord) oos.readObject(); 
        } catch(IOException | ClassNotFoundException ignore) {
        }
        return r;
    }
}