executeUpdate() returns always 1 with MERGE statement

ExecuteUpdate() is always returning 1. Pls suggest and appreciate any input.

Procedure:

PROCEDURE INSERT_USER_PREFERENCES(owner_id_var varchar2, stripeid_var varchar2, type_var varchar2, metadata_var CLOB) AS
BEGIN
    MERGE INTO CXO_USER_PREFERENCES d
    USING(SELECT stripeid_var id FROM dual) s
    ON (d.stripe_id = s.id)
    WHEN NOT MATCHED THEN
        INSERT (OWNER_ID, STRIPE_ID, PREF_TYPE, METADATA, CREATED_DATE )
        VALUES(owner_id_var, stripeid_var, type_var, metadata_var, CURRENT_TIMESTAMP);

    COMMIT;
END  INSERT_USER_PREFERENCES;

Java code:

try (CallableStatement stmt = connection.prepareCall(CREATE_USER_PREF_SQL)) {
        SQLParameterMapper sqlParamMapper = new SQLParameterMapper(CREATE_USER_PREF_SQL);
        sqlParamMapper.setString(stmt, ":ownerId", userName);
        sqlParamMapper.setCharacterStream(stmt, ":metadata", reader, metadata.length());
        sqlParamMapper.setString(stmt, ":stripeId", stripeId);
        sqlParamMapper.setString(stmt, ":type", userPreference.getType());

        // invoke the database
        int value = stmt.executeUpdate();
        return value;
    } catch (SQLException e) {
        throw e;
    }

stmt.executeUpdate() – always returns 1, even though insertion happens only once. Appreciate any inputs on this. Ideally, if there are no insertions or errors, it should return 0 or any exception trace. Pls, suggest.**

Answer

We can make the column a primary key. Now when multiple VM’s tries to execute, the other one will throw a Unique Integrity exception and in the catch block, we can read the SQLException. Once we read the SQLException, we can also read the sqlstate and we can perform the needed operations based on the sqlstate.

Sample code below

     if (e instanceof SQLException) {
            String sqlState = ((SQLException) e).getSQLState();
            if (sqlState.equalsIgnoreCase("08000") || sqlState.equalsIgnoreCase("08006")) {
                logger.error("POD DB is down with the sql state:" + sqlState);
                return true;
            }
        }