How to call a PL/SQL stored function through Java EntityManager

Question

I have this function

create or replace
FUNCTION IFRS_FUNCTION_TEST RETURN NUMBER IS

estado NUMBER;
excepciones NUMBER;
title_code VARCHAR2(150 char);
colgaap_code VARCHAR2(150 char);
ifrs_code VARCHAR2(150 char);
v_causacion VARCHAR2(150 char);
pyg_diario_value VARCHAR2(150 char);
fecha_valoracion VARCHAR2(150 char);
pyg_dialogo VARCHAR2(150 char);

CURSOR ifrscursor IS SELECT no_asignado_por_la_entidad, codigo_puc FROM IFRS_351 WHERE no_asignado_por_la_entidad LIKE 'W%' FOR UPDATE;

BEGIN
        BEGIN
        estado:=0;
        excepciones:=0;
        OPEN ifrscursor;
        LOOP
            FETCH ifrscursor INTO title_code, colgaap_code; 
            EXIT WHEN ifrscursor%NOTFOUND;
            SELECT cta_ifrs into ifrs_code FROM ifrs_tabla_homo_351 WHERE codigo_puc_colgaap = colgaap_code;
            UPDATE IFRS_351 SET codigo_puc = ifrs_code WHERE CURRENT OF ifrscursor;
            BEGIN
                SELECT saldo, pyg_diario into v_causacion, pyg_diario_value FROM IFRS_INV_OBL WHERE nro_titulo=title_code;
                UPDATE IFRS_351 SET vr_mercado_o_valor_presente_ = v_causacion, causacion_valoracion_cuentas = pyg_diario_value WHERE CURRENT OF ifrscursor;
            EXCEPTION WHEN NO_DATA_FOUND THEN
                excepciones:=excepciones+1;
            END;
END LOOP; 
CLOSE ifrscursor;
EXCEPTION WHEN OTHERS THEN
    estado:=SQLCODE;  
END;
RETURN(estado);

How can I get the estado value of this function with Java EntityManager? I tried with

SELECT IFRS_FUNCTION_TEST() FROM DUAL;

but I always obtain the error code 14551.

Answer 1

The value -14551 is actually an Oracle error rather than a value. The error is detailed below and should give more details on exactly why it's not working.

ORA-14551 cannot perform a DML operation inside a query Cause: DML operation like insert, update, delete or select-for-update cannot be performed inside a query or under a PDML slave. Action: Ensure that the offending DML operation is not performed or use an autonomous transaction to perform the DML operation within the query or PDML slave.

Answer 2

You cannot use a SELECT query to retrieve the value of a function containing DML statements. You have to use an explicit call. something like that (untested):

Object result = em.createNativeQuery("{ ? = CALL IFRS_FUNCTION_TEST() }")
                    .getSingleResult();

---

EDIT: as this seems to not work, an other option is to send that query directly through JDBC (via session.getConnection()). Something like that probably:

CallableStatement statement = session.connection().prepareCall(
        "{ ? = CALL IFRS_FUNCTION_TEST() }");
statement.registerOutParameter(1, Types.OTHER);
//                                      ^^^^^
//                       use the right type here of course
statement.execute();
Object result = statement.getObject(1);

See the doc of OracleCallableStatement for details.

Answer 3

Here's a solution that uses javax.persistence.EntityManager:

@PersistenceContext
private EntityManager entityManager;

. . .

int estado;
Session session = entityManager.unwrap(Session.class);
session.doWork(connection -> {
    try (CallableStatement function = connection.prepareCall(
            "{ ? = call IFRS_FUNCTION_TEST() }" )) {
        function.registerOutParameter( 1, Types.INTEGER );
        function.execute();
        estado = function.getInt(1);
    }
} );

These are the main dependencies to indicate what lib versions the solution works with:

compile 'com.oracle:ojdbc6:11.2.0.4.0'
compile 'org.hibernate:hibernate-core:5.4.7.Final'
compile 'org.hibernate.javax.persistence:hibernate-jpa-2.1-api:1.0.2.Final'

As an alternative to doWork() and assigning the stored function result with getXxx(1), you can use doReturningWork().

As others pointed out, you cannot use a (native) query because of the UPDATE (DML) command inside the function.

In Kotlin

Here's the solution in Kotlin (and using doReturningWork):

@PersistenceContext
lateinit var entityManager: EntityManager

. . .

val session : Session = entityManager.unwrap(Session::class.java)
var estado = session.doReturningWork { connection: Connection ->
    connection.prepareCall(
            "{ ? = call IFRS_FUNCTION_TEST() }").use { function ->
        function.registerOutParameter(1, Types.INTEGER)
        function.execute()
        return@doReturningWork function.getInt(1)
    }
}

Answer 4

JPA 2.0 doesn't support RETURN values, only calls. The JPA 2.1 fixed this,

If you're using JPA 2.0. My solution was. Create a FUNCTION calling PROCEDURE. So, inside JAVA code you execute a NATIVE QUERY calling the oracle FUNCTION.

or else use createStoredProcedureQuery