Java and JavaScript in the Database

Hi,

We have a Java Spring Boot application with Oracle database (we tested all XE versions from 12C to 23IA free edition).

We are encountering an error when trying to retrieve the generated numeric ID from an IDENTITY column using the JDBC driver. Specifically, the RETURN_GENERATED_KEYS functionality.

The table is defined as:

CREATE TABLE tst_order
(
idOrder NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_name VARCHAR2(100) NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Debugging in the driver source coder (last version of jdbc11), we see that the metadata for the column provided by the database is BLOB. And the value returned by the method is a string of the type "AAASh0AABAAAF6xAAA" instead of the numeric value.

String insertOrderSql = "INSERT INTO tst_order (customer_name) VALUES (?)";
Long generatedOrderId;

try (PreparedStatement stmt = conn.prepareStatement(insertOrderSql,   Statement.RETURN_GENERATED_KEYS)) {
stmt.setString(1, "John Doe");
stmt.executeUpdate();

try (ResultSet keys = stmt.getGeneratedKeys()) {
assertThat(keys.next()).isTrue();
generatedOrderId = keys.getLong(1);
}
}

The stack error trace is:

java.sql.SQLException: ORA-17132: Se ha solicitado una conversión no válida
https://docs.oracle.com/error-help/db/ora-17132/

	at oracle.jdbc.driver.T4CVarcharAccessor.StringToNUMBER(T4CVarcharAccessor.java:797)
	at oracle.jdbc.driver.T4CVarcharAccessor.getNUMBER(T4CVarcharAccessor.java:264)
	at oracle.jdbc.driver.T4CVarcharAccessor.getLong(T4CVarcharAccessor.java:565)
	at oracle.jdbc.driver.GeneratedStatement.getLong(GeneratedStatement.java:179)
	at oracle.jdbc.driver.GeneratedScrollableResultSet.getLong(GeneratedScrollableResultSet.java:275)
	at com.zaxxer.hikari.pool.HikariProxyResultSet.getLong(HikariProxyResultSet.java)
	at com.goplan.oracleautoincrement.OracleAutoincrementApplicationTests.testInsertOrderAndItems(OracleAutoincrementApplicationTests.java:36)
	at java.base/java.lang.reflect.Method.invoke(Method.java:569)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
Caused by: java.lang.NumberFormatException: Character A is neither a decimal digit number, decimal point, nor "e" notation exponential mark.
	at java.base/java.math.BigDecimal.<init>(BigDecimal.java:586)
	at java.base/java.math.BigDecimal.<init>(BigDecimal.java:471)
	at java.base/java.math.BigDecimal.<init>(BigDecimal.java:900)
	at oracle.jdbc.driver.T4CVarcharAccessor.StringToNUMBER(T4CVarcharAccessor.java:792)
	... 9 more

Does anyone have any idea what might be going on?

Regards.