Unexpected IncorrectResultSizeDataAccessException when querying Oracle db

Question

I have following table:

  create table INTERNATIONALIZATION (
    ID number not null unique,
    LANG char(2) not null,
    EXT_ID number not null,
    EXT_COLUMN char(32) not null,
    EXT_NAME char(32) not null,
    TRANS_VAL nvarchar2(512) not null
  );

And following code that aims to retrieve one and only one result from it (I am 100% sure that record exists).

public Optional<String> getTranslation(long idSkill, Locale lang, String extColumn, String extName) {
    try {
        return Optional.of(jdbcTemplate.queryForObject("select TRANS_VAL from INTERNATIONALIZATION where ext_id = ? and lang = ? and ext_column = ? and ext_name = ?", String.class, idSkill, lang.toLanguageTag(), extColumn, extName));
    } catch (IncorrectResultSizeDataAccessException ex) {
        return Optional.empty();
    }
}

The problem is that when I always get the Optional.empty() as the IncorrectResultSizeDataAccessException is thrown, because no records are found.

When I execute this sql query in Oracle SQL Developer, I get the correct result.

What is causing this problem? Could it have something to do with nvarchar2 type of desired column?

Answer 1

You probably need to verify the arguments passed to the query. They may not be the same as those you used at Oracle SQL Developer.

For debugging, when you catch the exception, print the values used.

    try {
            return Optional.of(jdbcTemplate.queryForObject("select TRANS_VAL from INTERNATIONALIZATION where ext_id = ? and lang = ? and ext_column = ? and ext_name = ?", String.class, idSkill, lang.toLanguageTag(), extColumn, extName));
        } catch (IncorrectResultSizeDataAccessException ex) {
            return Optional.empty();
          // print idSkill, lang.toLanguageTag(), extColumn, extName here
        }

Also, as you are using CHAR columns, you have to take care with the white spaces padded when comparing. As you are using bind variables, there might be a issue when comparing chars against varchars.

Created a test table:
SQL> create table t1 (c1 char(5));

    Table created.

    SQL> insert into t1 values ('A');

    1 row created.

    SQL> commit;

    Commit complete.

Comparing with literals, no problem:

    SQL> select count(*) from t1 where c1 = 'A'; 

      COUNT(*)
    ----------
         1

    SQL> select count(*) from t1 where c1 = 'A ';

      COUNT(*)
    ----------
         1

As the c1 is a char column, the data is padded to the left:

    SQL> select '<'|| c1 || '>' from t1;

    '<'||C1
    -------
    <A    >

When comparing with a varchar2 bind variable

    SQL> var s varchar2(5)
    SQL> exec :s := 'A' ;

    PL/SQL procedure successfully completed.

    SQL> select count(*) from t1 where c1 = :s;

      COUNT(*)
    ----------
         0

Data has to be equal (5 characteres - one letter, 4 spaces):

    SQL> exec :s :='A    '

    PL/SQL procedure successfully completed.

    SQL> select count(*) from t1 where c1 = :s;

      COUNT(*)
    ----------
         1

That's just a tip, but I think you would be better off using varchar2 columns:

      create table INTERNATIONALIZATION (
        ID number not null unique,
        LANG varchar2(2) not null,
        EXT_ID number not null,
        EXT_COLUMN varchar2(32) not null,
        EXT_NAME varchar2(32) not null,
        TRANS_VAL nvarchar2(512) not null
      );