oracle-databaseojdbceclipse-temurin

The same java code does not work for all JDK


I have a code:

@PersistenceContext
private EntityManager em;

public void call() {


    String input = "<ROOT>" +
                       "<START_DATE>20240824</START_DATE>" +
                    "</ROOT>";

    StoredProcedureQuery proc = em.createStoredProcedureQuery("procedureName");
    proc.registerStoredProcedureParameter("inXml", String.class, ParameterMode.IN);
    proc.registerStoredProcedureParameter("outXml", String.class, ParameterMode.OUT);
    proc.registerStoredProcedureParameter("error", String.class, ParameterMode.OUT);

    proc.setParameter("inXml", input);

    proc.execute();

    var outXml = (String) proc.getOutputParameterValue("outXml");
    var error = (String) proc.getOutputParameterValue("error");

    log.info(outXml);
    log.info(error);

}

This is a Spring Boot ( version 3.0.7 ) application. I call stored procedure on Oracle db with date as a parameter. I run the code on two computers:

1. java installed = openjdk version "17.0.9" 2023-10-17 OpenJDK Runtime Environment Temurin-17.0.9+9 (build 17.0.9+9) OpenJDK 64-Bit Server VM Temurin-17.0.9+9 (build 17.0.9+9, mixed mode, sharing)

2. java installed = openjdk version "17.0.2" 2022-01-18 OpenJDK Runtime Environment (build 17.0.2+8-86) OpenJDK 64-Bit Server VM (build 17.0.2+8-86, mixed mode, sharing)

Number 1 works fine but in number 2 the error comes up = ORA-01861: literal does not match format string

Could you tell me why the same code does not work on server number 2 ?


Solution

  • The programs are connecting to the same server but two connections each start a different session and those sessions have (for some reason) got different NLS session parameters.

    Somewhere in your procedure you are performing an implicit conversion from a string to a DATE:

    The solution is to NEVER rely on implicit conversions.

    You do not provide the source code for your procedure but if you use TO_DATE(value), change it to use an explicit format model TO_DATE(value, 'YYYYMMDD'). Similarly if you are relying on implicit conversions in XML functions - don't rely on the implicit conversion; make it explicit.


    An alternative solution is create a logon trigger so that at the start of every session you run:

    ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD';
    

    To set the format model that Oracle uses for implicit string-to-date (and vice versa) conversions. However, this is not best practice and you should fix your code not to rely on implicit conversions.