ORA-17026: Numeric overflow when calling Oracle SQL function using Hibernate 6

A few days ago we stumbled upon a very strange error while migrating a legacy application from Spring Boot 2.7.0 with Spring Data JPA (Hibernate Core 5.6.9-Final) to Spring Boot 3.1.0 (Hibernate Core 6.2.2-Final)

The code was executing an Oracle database function, called pl_functions.persist_bonus_points which was persisting some loyalty customer points for a bank and returned a transaction ID. The function call used to work before the upgrade. After upgrading to Hibernate 6 it started failing with an error:

Could not extract column [1] from JDBC ResultSet [ORA-17026: Numeric overflow] [n/a] with root cause

After quite some investigation, we found out that when pl_functions.persist_bonus_points() function is called, it returns a value which looks like this: 4807214298310656894. Hibernate 6 is trying to convert it to number and it fails with an overflow. In our case we could not rewrite the code because we could not understand exactly what it was doing. After searching in the web, we found in StackOverflow question #36073450 that the least intrusive way to patch the existing code was by using .addScalar() method and convert the returned value to String.

After the fix the code is the following:

public String getTransactionIdForUpdatingBonusPoints(Long branchId, String customerId, String points) {
    Query query = entityManager.createNativeQuery("select pl_functions.persist_bonus_points (:branchId, :customerId, :points) as transactionId from dual");
    
    query.setParameter("branchId", branchId);
    query.setParameter("customerId", customerId);
    query.setParameter("points", points);

    // FIX FOR HIBERNATE 6
    query.unwrap(NativeQuery.class)
        .addScalar("transactionId", StandardBasicTypes.STRING);

    // THIS IS WHERE THE ERROR OCCURED
    var res = query.getResultList();
    return getTransactionIdFromResult(res);
}

I hope that this helps in your Hibernate migration endeavours! Cheers from Thessaloniki, Greece!