'Spring Data JPA calling Oracle Function

I am running an simple application that uses Spring Boot + Spring Data JPA for persistence.

Below is a sample Oracle function I would like to have the value returned at the Service implementation class.

CREATE OR REPLACE PACKAGE PKG_TEST AS 
  FUNCTION HELLO_WORLD(TEXT VARCHAR2) RETURN VARCHAR2;
END PKG_TEST;

CREATE OR REPLACE PACKAGE BODY PKG_TEST AS 
  FUNCTION HELLO_WORLD(TEXT VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN 'HELLO WORLD ' || TEXT;
  END;
END PKG_TEST;

Doing this with no framework would be simple, but the project is built into Spring Boot JPA, so it's better to use it.

I need a reference guide link or simple base structure to follow. I searched all over SO and Spring Data JPA reference and all examples I found are for CRUD and Stored Procedures, nothing for Functions.

I tried to use the Stored procedure example modified for function but didn't work.

javaspringoraclefunctionjpa


Solution 1:[1]

If you are using Hibernate as JPA Provider you may create custom dialect and register needed function.

public class CustomDialect extends Oracle10gDialect {
    public CustomDialect() {
        super();
        // CustomFunction implements SqlFunction
        registerFunction("custom_function", new CustomFunction());
        // or use StandardSQLFunction; useful for coalesce
        registerFunction("coalesce", new StandardSQLFunction("coalesce"));
    }
}

Solution 2:[2]

using entityManager.createNativeQuery works.

See: How to call a custom Oracle function returning a value from JPA

and the referenced page: https://vladmihalcea.com/how-to-call-oracle-stored-procedures-and-functions-from-hibernate/

Solution 3:[3]

public interface inteface-name extends CrudRepository<Object,Long> {

    @Procedure(procedureName = "procedure-name", outputParameterName = "param-out-name")
    BigDecimal method-name(dataType input-param);
}

Solution 4:[4]

Incase if you need to return a set of columns with n number of rows returned as a oracle type from a function, Use Table(function_name) with the select statement to parse the type as table and get as list of object array List.

@Query(nativeQuery = true, value = "SELECT * FROM TABLE(ListOfStates(:country))")
List<Object[]> findStatesByCountry(@Param("country") String country);

Solution 5:[5]

 this.entityManager.getSession().doWork(connection -> {
        try (CallableStatement query = connection
                .prepareCall("BEGIN ? := FUNCTION_NAME(?); END;")) {
            query.registerOutParameter("PARAM1", Types.VARCHAR); // out param
            query.setString("PARAM2"), "SOME_VALUE"); // in param
            query.execute();
            String value = query.getString("PARAM1"); //outparam result
            System.out.println(value);
        } catch (Exception ex) {
           //process exception here
        }
    });

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Dmytro
Solution 2 Vlad Mihalcea
Solution 3 Eric Aya
Solution 4 Sarvan_C
Solution 5 Thofiq

Related Questions