oracle-databasejdbcspring-jdbcojdbc

Springs's KeyHolder, oracle jdbс and insert-query with line breaks lead to ORA-00931: missing identifier

I store sql-queries in resource files with pretty human-readable formatting(with line breaks and indents). Suddenly I faced with issue: simple query, like below, works fine without fetching generated code-column value via keyholder,

insert into 
my_table
(
  code
)
values
(
  my_table_seq.nextval
)

but return ORA-00931: missing identifier\n when keyholder used:

...
var keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
        "MY INSERT QUERY",
        new MapSqlParameterSource(),
        keyHolder,
        new String[]{"code"}
);
...

I debugged oracle jdbc driver(ojdbc8/ojdbc10 19.24) and noticed that whitespace after into-keyword cause this behaviour: AutoKeyInfo.getTableName return "\n" instead of "MY_TABLE"(see image below)! Removing whitespace solved the issue.

Is it bug(and is it possible to submit it to ojdbc developers)? keyHolder fail

Solution

  • Is it a bug?

    Yes... The code looks for INSERT then INTO and then skips space characters and assumes that the next non-space character is the start of the identifier for the table name. When it finds a new-line character, it assumes that is the start of the identifier and returns a single new-line character instead of the actual identifier.

    What it should do is skip whitespace characters rather than just spaces. (There are more issues with that method including: changing the case of the identifier; looking for either a space character or ( but not other whitespace characters at the end of the identifier; and looking for INSERT then INTO anywhere in the string rather than only at the start.)

    However... The work-around is simple, don't use non-space whitespace characters in INSERT INTO my_table (, just use spaces between the keywords and the identifier (and it also won't work with mixed-case identifiers surrounded by quotes, just use unquoted identifiers).

    So, yes, you could report it but, in the meanwhile, you should be able to easily fix your code so it doesn't trigger the issue.