We have to refracgtor some code used for integration with another application which runs in Oracle DB and exposes to us some stored procedures or functions. Actually the execution of those stored procedures is done via Session of Hibernate but for some security issues we are required to strictly use the generic JPA interfaces and EntityManager.
Unfortunately, we are facing some problems because we are not able to understand which is the right way to proceed. This is the signature of the Oracle stored function which is inside a package
CREATE OR REPLACE package body DEVELOPERS.INTEGRATION_OPERATIONS is
...
function doOperation(arg1_in in number,
arg2_in in varchar2,
arg3_in in date,
arg4_in in number,
arg5_in in number,
arg6_in in date,
arg7_in in varchar2 default null,
arg8_in in varchar2 default null,
arg9_in in varchar2 default null,
arg10_in in varchar2,
arg11_in in number default null,
arg12_in in number
) return number is
...
end doOperation;
begin
...
end INTEGRATION_OPERATIONS;
The first trial I did was to use JPA StoredProcedureQuery by reading what I found here and other similar posts. But it does not seem to work: it fails with an error saying that the procedure is not defined. My guess is that it expects a really stored procedure while mine is a function.
The only way which seems to work is the following:
public enum OPERATION_PARAMATERS {
PARAM_ARG1(1,"arg1_in", Long.class, ParameterMode.IN),
PARAM_ARG2(2, "arg2_in", String.class, ParameterMode.IN),
PARAM_ARG3(3, "arg3_in", Timestamp.class, ParameterMode.IN),
PARAM_ARG4(4, "arg4_in", Long.class, ParameterMode.IN),
PARAM_ARG5(5, "arg5_in", Long.class, ParameterMode.IN),
PARAM_ARG6(6, "arg6_in", Timestamp.class, ParameterMode.IN),
PARAM_ARG7(7, "arg7_in", String.class, ParameterMode.IN),
PARAM_ARG8(8, "arg8_in", String.class, ParameterMode.IN),
PARAM_ARG9(9, "arg9_in", String.class, ParameterMode.IN),
PARAM_ARG10(10, "arg10", String.class, ParameterMode.IN),
PARAM_ARG11(11, "arg11_in", Long.class, ParameterMode.IN),
PARAM_ARG12(12, "arg12_in", Long.class, ParameterMode.IN);
private final int paramPosition;
private final String paramName;
private final Class<?> paramType;
private final ParameterMode paramMode;
<T> OPERATION_PARAMATERS(int paramPosition, String paramName, Class<T> clazz, ParameterMode paramMode) {
this.paramPosition = paramPosition;
this.paramName = paramName;
this.paramType = clazz;
this.paramMode = paramMode;
}
public int getParamPosition() {
return paramPosition;
}
public String getParamName() {
return paramName;
}
public Class<?> getParamType() {
return paramType;
}
public ParameterMode getParamMode() {
return paramMode;
}
}
@Override
public void invokeOperation(final DoOperation operation) {
Query nativeQuery = this.entityManager.createNativeQuery(DoOperation.STORED_FUNCTION_CALL);
for(OPERATION_PARAMATERS p : OPERATION_PARAMATERS.values()) {
nativeQuery.setParameter(p.getParamName(), operation.getParamValue(p));
}
nativeQuery.executeUpdate();
}
where DoOperation is a sort of DTO containing all values for parameters and the native query is
public static final String STORED_FUNCTION_CALL = "DECLARE res NUMBER; begin res \\:= INTEGRATION_OPERATIONS.DOOPERATION(:arg1_in, :arg2_in, :arg3_in, :arg4_in, :arg5_in, :arg6_in, :arg7_in, :arg8_in, :arg9_in, :arg10_in, :arg11_in, :arg12_in); END;";
This seems to be executed without errors, but supposing this is the right way to do, how can I recover the returned value in res variable? I cannot ask the other developers to refactor this function as a stored procedure with in/out paramaters as it is a legacy integration.
How can I do? Is there a way to use JPA StoredProcedureQuery or is it reserved to pure stored procedures?
Our application is a Java EE 7 application running under Wildfly 10 with EJB 3.1. The implementation of JPA is provided by Wildfly with hibernate-core-4.1.2.Final.jar
