Call Oracle StoredProcedure with multiple output parameters

user_2805479 0
Viewed 1648

I have this special case. I have an Oracle stored-procedure that doesn't receive IN parameters but returns 4 OUT parameters.

My entity class is this ...

    @Entity
    @Table(name = "TABLE_NAME")
    @NamedStoredProcedureQueries({
      @NamedStoredProcedureQuery(
        name = "MyPackage.spName",
        procedureName = "MYPACKAGE.SPNAME",
        parameters = {
          @StoredProcedureParameter(mode = ParameterMode.OUT, name = "PO_VALUE1", type = String.class),
          @StoredProcedureParameter(mode = ParameterMode.OUT, name = "PO_VALUE2", type = String.class),
          @StoredProcedureParameter(mode = ParameterMode.OUT, name = "PO_VALUE3", type = String.class),
          @StoredProcedureParameter(mode = ParameterMode.OUT, name = "PO_VALUE4", type = String.class)
        })
    
    })
    public class Tablename {    
        // omitted properties ...    
    }

My Repository class is this ...

@Repository
public interface TableNameRepository extends JpaRepository<TableName, Long> {

  @Procedure(name = "MyPackage.spName")
  Map<String, Object> spName();

}

And I'm calling the stored procedure like this ...

  Map<String, Object> spNameMap = tableNameRepository.spName();
  String value1 = spNameMap.get("PO_VALUE1").toString();
  String value2 = spNameMap.get("PO_VALUE2").toString();
  // more values ...

But it throws this exception ...

Positional parameter [1] is not registered with this procedure call;

2021-12-29 18:55:13,630 ERROR [http-nio-3000-exec-1] pe.gob.onpe.wsonpe.service.VersionService: InvalidDataAccessApiUsageException: Positional parameter [1] is not registered with this procedure call; nested exception is java.lang.IllegalArgumentException: Positional parameter [1] is not registered with this procedure call
2021-12-29 18:55:13,633 ERROR [http-nio-3000-exec-1] pe.gob.onpe.wsonpe.service.VersionService: org.springframework.dao.InvalidDataAccessApiUsageException: Positional parameter [1] is not registered with this procedure call; nested exception is java.lang.IllegalArgumentException: Positional parameter [1] is not registered with this procedure call
    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:374)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:235)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
    at com.sun.proxy.$Proxy123.spFecha(Unknown Source)
    at pe.gob.onpe.wsonpe.service.VersionService.verificarVersion(VersionService.java:32)
    at pe.gob.onpe.wsonpe.controller.VersionController.userLogin(VersionController.java:44)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:681)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1722)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.IllegalArgumentException: Positional parameter [1] is not registered with this procedure call
    at org.hibernate.query.procedure.internal.ProcedureParameterMetadata.getQueryParameter(ProcedureParameterMetadata.java:141)
    at org.hibernate.procedure.internal.ProcedureCallImpl.getParameterRegistration(ProcedureCallImpl.java:311)
    at org.hibernate.procedure.internal.ProcedureOutputsImpl.getOutputParameterValue(ProcedureOutputsImpl.java:53)
    at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputParameterValue(ProcedureCallImpl.java:656)
    at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.extractOutputParameterValue(StoredProcedureJpaQuery.java:158)
    at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.extractOutputValue(StoredProcedureJpaQuery.java:142)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$ProcedureExecution.doExecute(JpaQueryExecution.java:335)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:90)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:159)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:138)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
    ... 61 more

It seems that JPA is trying to map parameters by order instead by name. This behaviour is weird, since I'm using parameter names with the annotation @StoredProcedureParameter

Please internet, help me with this.

spring-boot jpa stored-procedures oracle java
2 Answers

Spring boot JPA starter calls StoredProcedureJpaQuery class on initialization. That class contains a property called useNamedParameters.

Calling your stored procedure like this ...

@Procedure(name = "MyPackage.spName")
Map<String, Object> spName();

means that spring JPA will map your IN and OUT parameters by position To avoid this, you have to use the annotation @Param in your repository, like this ...

@Procedure(name = "MyPackage.spName")
Map<String, Object> spName(@Param("PO_VALUE1") String value1);

using the @Param annotation will set the useNamedParameters property to true and Spring JPA will not start looking by position instead by name. But, I don't have any IN parameters in my query. So I changed the method to INOUT like this ...

@StoredProcedureParameter(mode = ParameterMode.INOUT, name = "PO_VALUE1", type = String.class)

and passing an empty string when I call the repository ...

Map<String, Object> spNameMap = tableNameRepository.spName("");

That solution works for me, but I'm open to see a better solution for this.

user_28054790

I can't really answer your specific question about how to do these things with JPA and Spring Data, although maybe, you don't have to?

Using JDBC for this, directly

JPA and Spring Data don't really offer too much value on top of JDBC for ordinary stored procedures. It's just a different way to manually configure the bindings. So, in JDBC, you'd write this:

try (CallableStatement s = c.prepareCall(
    "{ call mypackage.spname(?, ?, ?, ?) }"
)) {
    s.registerOutParameter(1, Types.VARCHAR);
    s.registerOutParameter(2, Types.VARCHAR);
    s.registerOutParameter(3, Types.VARCHAR);
    s.registerOutParameter(4, Types.VARCHAR);
    s.executeUpdate();
    String value1 = s.getString(1);
    String value2 = s.getString(2);
}

I can empathise with the idea that once you use Spring Data JPA and JPA in some areas, you want to use it everywhere. But I just want to remind folks that Spring Data just wraps JDBC, and in the case of stored procedure calls (unless the procedure returns a set of entities), it doesn't really simplify anything, it just consolidates the programming model.

Using a third party library for stored procedure calls

If using another third party library is an option for you, why not try jOOQ, which has a code generator to represent your entire schema, including packages, functions, procedures, UDTs, etc as Java code. With jOOQ, you can just write:

Configuration configuration = // Wrapping your JDBC Connection;
Spname result = Mypackage.spname(configuration);
String value1 = result.getPoValue1();
String value2 = result.getPoValue2();

Thanks to code generation, everything is type safe, just like when you use XJC to generate stubs for your WSDL endpoints. When you modify the procedure signature, your Java code stops compiling and you can refactor your call code.

Disclaimer: I work for the company behind jOOQ.

user_5217990
Related