Spring Data JPA calling Oracle Function

Question

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.

I don't think there is support for sql functions, there are ways to call them but not to create and store them as far as I know. The JPA specification hasn't such contract from the javadoc, I doubt it's being implemented by any persistence provider — Thomas.L
– Thomas.L, Commented Aug 24, 2017 at 17:27

Michał Stochmal · Accepted Answer · 2024-04-20 19:13:12Z

16

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));
    }

}

edited Apr 20, 2024 at 19:13

answered May 14, 2018 at 11:02

Michał Stochmal

6,7514 gold badges40 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sh4d0wsPlyr Over a year ago

I cant propose an edit for a single character, but there is an extra ")" on your .addValue line.

Sarvan_C · Accepted Answer · 2019-06-26 13:13:52Z

1

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);

answered Jun 26, 2019 at 13:13

Sarvan_C

112 bronze badges

1 Comment

Lazcool Over a year ago

What happens to this query if the ListOfStates function also takes an OUT parameter (let say myOutParam) please ??

Dmytro · Accepted Answer · 2017-09-12 13:16:08Z

0

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"));
    }
}

answered Sep 12, 2017 at 13:16

Dmytro

831 silver badge7 bronze badges

Comments

Vlad Mihalcea · Accepted Answer · 2018-01-06 10:17:01Z

0

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/

edited Jan 6, 2018 at 10:17

Vlad Mihalcea

156k85 gold badges599 silver badges984 bronze badges

answered Aug 24, 2017 at 17:40

D.S.

1038 bronze badges

Comments

Eric Aya · Accepted Answer · 2018-10-22 10:19:57Z

0

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

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

edited Oct 22, 2018 at 10:19

Eric Aya

70.2k36 gold badges190 silver badges266 bronze badges

answered Oct 22, 2018 at 10:13

Soodabeh Neirizi

794 bronze badges

Comments

Thofiq · Accepted Answer · 2020-07-11 13:36:56Z

0

 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
        }
    });

answered Jul 11, 2020 at 13:36

Thofiq

492 bronze badges

Collectives™ on Stack Overflow

Spring Data JPA calling Oracle Function

6 Answers 6

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related