title: Error while calling an Oracle PL/SQL function from Spring Boot
body: <p>I have a <code>Spring Boot</code> application that is connected to an <code>Oracle</code> DB.
  This <code>DB</code> has a bunch of stored procedures and functions written in <code>PL/SQL</code>.</p>
  
  <p>I am trying to call a very simple <code>PL/SQL</code> function that takes a string as input and returns a string as output. But I am getting an exception while calling it.</p>
  
  <p><strong>Error:</strong></p>
  
  <pre><code>PLS-00306: wrong number or types of arguments in call to 'CHECK_ACTIVE_APPL'
  ORA-06550: line 1, column 7:
  PL/SQL: Statement ignored
  
  2019-03-05 11:14:57.456 ERROR WCD1122021 --- [nio-9090-exec-1] o.a.c.c.C.[.[.[.[dispatcherServlet]      : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: Error calling CallableStatement.getMoreResults; SQL [PKG_USROUTER.CHECK_ACTIVE_APPL]; nested exception is org.hibernate.exception.SQLGrammarException: Error calling CallableStatement.getMoreResults] with root cause
  java.sql.SQLException: ORA-06550: line 1, column 7:
  PLS-00306: wrong number or types of arguments in call to 'CHECK_ACTIVE_APPL'
  ORA-06550: line 1, column 7:
  PL/SQL: Statement ignored
  
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
      at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
      at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
      at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
      at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
      at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:205)
      at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1043)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
      at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3714)
      at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4755)
      at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1378)
      at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
      at com.zaxxer.hikari.pool.HikariProxyCallableStatement.execute(HikariProxyCallableStatement.java)
      at org.hibernate.result.internal.OutputsImpl.&lt;init&gt;(OutputsImpl.java:52)
      at org.hibernate.procedure.internal.ProcedureOutputsImpl.&lt;init&gt;(ProcedureOutputsImpl.java:34)
      at org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:453)
      at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputs(ProcedureCallImpl.java:404)
      at org.hibernate.procedure.internal.ProcedureCallImpl.outputs(ProcedureCallImpl.java:663)
      at org.hibernate.procedure.internal.ProcedureCallImpl.execute(ProcedureCallImpl.java:646)
      at org.springframework.data.jpa.repository.query.JpaQueryExecution$ProcedureExecution.doExecute(JpaQueryExecution.java:332)
      at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:91)
      at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:136)
      at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:125)
      at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:590)
      at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:578)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
      at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
      at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
      at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
      at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
      at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
      at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
      at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
      at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
      at com.sun.proxy.$Proxy121.checkActiveAppl(Unknown Source)
      at com.example.demo.service.LinkApplService.checkActiveAppl(LinkApplService.java:47)
      at com.example.demo.controller.LinkApplController.getAllApplLinks(LinkApplController.java:31)
      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:209)
      at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
      at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102)
      at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:870)
      at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:776)
      at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
      at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:991)
      at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:925)
      at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:978)
      at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:870)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:635)
      at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:855)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
      at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
      at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
      at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:109)
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
      at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81)
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
      at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200)
      at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
      at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:496)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
      at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)
      at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
      at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:790)
      at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459)
      at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
      at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
      at java.lang.Thread.run(Thread.java:748)
  </code></pre>
  
  <p><strong>PL/SQL Function:</strong></p>
  
  <pre><code>FUNCTION CHECK_ACTIVE_APPL (appl IN VARCHAR2)
  RETURN VARCHAR2 AS
    activeFlag VARCHAR2(1);
    BEGIN
      SELECT appl_actv_cd INTO activeFlag FROM   link_appl WHERE  appl_id=appl;
      RETURN activeFlag;
  END CHECK_ACTIVE_APPL;  
  </code></pre>
  
  <p><strong>LinkApplController.java</strong></p>
  
  <pre><code>@Controller
  @RequestMapping("/applications")
  @Log4j2
  public class LinkApplController {
  
      @Autowired
      private LinkApplService linkApplService;
  
      @GetMapping
      public String getAllApplLinks(Model model) {
          String activeFlag = linkApplService.checkActiveAppl("TITAN");
          log.debug("@@@@@@@@@@@@@@@@activeFlag: " + activeFlag);
          return "index";
      }
  }   
  </code></pre>
  
  <p><strong>LinkApplService.java</strong></p>
  
  <pre><code>@Service
  public class LinkApplService {
  
      @Autowired
      private LinkApplRepository linkApplRepository;
  
      public String checkActiveAppl(String linkApplId) {
          return linkApplRepository.checkActiveAppl(linkApplId);
      }
  
  }
  </code></pre>
  
  <p><strong>LinkApplRepository.java</strong></p>
  
  <pre><code>@RepositoryRestResource
  public interface LinkApplRepository extends PagingAndSortingRepository&lt;LinkAppl, String&gt; {
  
      @Procedure(name = "checkActiveAppl")
      String checkActiveAppl(@Param("linkApplId") String linkApplId);
  
  }
  </code></pre>
  
  <p><strong>LinkAppl.java</strong></p>
  
  <pre><code>@Entity
  @Table(name = "LINK_APPL")
  @NamedStoredProcedureQueries({
          @NamedStoredProcedureQuery(
                  name = "checkActiveAppl",
                  procedureName = "PKG_USROUTER.CHECK_ACTIVE_APPL",
                  parameters = {
                          @StoredProcedureParameter(name = "linkApplId", type = String.class, mode = ParameterMode.IN),
                          @StoredProcedureParameter(name = "activeFlag", type = String.class, mode = ParameterMode.OUT)
                  }
          )
  })
  public class LinkAppl {
      private String applId;
      private String applDeTx;
      private String seqNbResetCd;
      private String intfMsgFrmtCd;
      private String sndRcvCd;
      private String applAcptMsgFrmtCd;
      private String applActvCd;
      private String sodEodIn;
      private String linkNotesTx;
      private String lastModByUsrId;
      private Timestamp lastModTs;
      private String sndCnctStsCd;
      private Long maxSeqNb;
      private String batIntfIn;
      private String gfpSrcSiteCd;
      private String rcvRterAckmentIn;
      private String rcvMqCodIn;
      private String fxApplIn;
      private String rcvEodpAckmentIn;
  }
  </code></pre>