[SOLVED] Spring Data JPA calling Oracle Function

Issue

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.

Solution

You can call your function via native query and get result from dual.

public interface HelloWorldRepository extends JpaRepository<HelloWorld, Long> {

    @Query(nativeQuery = true, value = "SELECT PKG_TEST.HELLO_WORLD(:text) FROM dual")
    String callHelloWorld(@Param("text") String text);

}

Note that it won’t work if your function is using DML statements. In this case you’ll need to use @Modifying annotation over query, but then the function itself must return number due to @Modifying return type restrictions.

You can also implement your CustomRepository and use SimpleJdbcCall:

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Repository;

@Repository
public class HelloWorldRepositoryImpl implements HelloWorldRepositoryCustom {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public String callHelloWorld() {
        SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
                .withCatalogName("PKG_TEST") //package name
                .withFunctionName("HELLO_WORLD");
        SqlParameterSource paramMap = new MapSqlParameterSource()
                .addValue("param", "value"));
        //First parameter is function output parameter type.
        return jdbcCall.executeFunction(String.class, paramMap));
    }

}

Answered By – Michał Stochmal

Answer Checked By – Terry (BugsFixing Volunteer)