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

Question

I have a custom function in oracle returning a number after deleting records. I could get a value in sql_plus such as

call my_function(4) into :out_number;

where out_number is a variable defined as number.

I could verify out_number has a value when I do "PRINT OUT_NUMBER."

My question is how to call this function from JPA.

I've tried like

Query query = em.createNativeQuery("{call my_function(?)}");
query.serParameter(1, 4);
return query.executeUpdate();

and got an error basically my_function is not defined. How can I get a return value as in CALL...INTO on SQL_PLUS?

If this method is not desirable, can someone please recommend any suggestion? JPA is the only option for me at this moment. I could create a stored procedure but I'm not sure if I can get a return value from it since OUT parameters are not supported.

Thanks for your help in advance!

Solution 1

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

First, we can simply call the Oracle function just like any other SQL query:

BigDecimal commentCount = (BigDecimal) entityManager
    .createNativeQuery(
        "SELECT fn_count_comments(:postId) FROM DUAL"
    )
    .setParameter("postId", 1L)
    .getSingleResult();

Another approach is to call the database function using plain JDBC API:

Session session = entityManager.unwrap( Session.class );

final AtomicReference<Integer> commentCount = 
    new AtomicReference<>();

session.doWork( connection -> {
    try (CallableStatement function = connection
            .prepareCall(
                "{ ? = call fn_count_comments(?) }"
            )
        ) {
        function.registerOutParameter( 1, Types.INTEGER );
        function.setInt( 2, 1 );
        function.execute();
        commentCount.set( function.getInt( 1 ) );
    }
} );

Solution 2

I used this to execute native functions in oracle:

Query query = em.createNativeQuery("select my_function(:param) from dual");
query.setParameter("param", 4);
return query.getSingleResult();

Comments

[James Shin]

Tulskiy, I can't use 'select ... from dual' because my_function performs delete statements (DML). I have tried to use BEGIN my_function(?); END; but, I have no luck to get a return value.

[Artur P]

Above code works for functions that modify data in database (use DML statements).

[Vadzim]

I guess this is only relevant for EclipseLink.

[WitnessTruth]

From where do you get this StoredFunctionCall object?