0

I have a Spring Boot application that is connected to an Oracle DB. This DB has a bunch of stored procedures and functions written in PL/SQL.

I am trying to call a very simple PL/SQL function that takes a string as input and returns a string as output. But I am getting an exception while calling it.

Error:

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.<init>(OutputsImpl.java:52)
    at org.hibernate.procedure.internal.ProcedureOutputsImpl.<init>(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)

PL/SQL Function:

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;

LinkApplController.java

@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";
    }
}

LinkApplService.java

@Service
public class LinkApplService {

    @Autowired
    private LinkApplRepository linkApplRepository;

    public String checkActiveAppl(String linkApplId) {
        return linkApplRepository.checkActiveAppl(linkApplId);
    }

}

LinkApplRepository.java

@RepositoryRestResource
public interface LinkApplRepository extends PagingAndSortingRepository<LinkAppl, String> {

    @Procedure(name = "checkActiveAppl")
    String checkActiveAppl(@Param("linkApplId") String linkApplId);

}

LinkAppl.java

@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;
}
Improve this question
10
  • Just added the model class above with @NamedStoredProcedureQueries annotation as I missed it earlier. Commented Mar 5, 2019 at 16:41
  • On your entity you are declaring two parameters: linkApplId and activeFlag, could you remove activeFlag and test it? Commented Mar 5, 2019 at 16:46
  • Ok, let me try.. Commented Mar 5, 2019 at 16:48
  • Error at server startup: Caused by: java.lang.IllegalArgumentException: Failed to create query for method public abstract java.lang.String com.example.demo.repository.LinkApplRepository.checkActiveAppl(java.lang.String)! No property checkActiveAppl found for type LinkAppl! Commented Mar 5, 2019 at 16:54
  • You should have something like: @NamedStoredProcedureQueries({ @NamedStoredProcedureQuery( name = "checkActiveAppl", procedureName = "PKG_USROUTER.CHECK_ACTIVE_APPL", resultClasses = { String.class }, parameters = { @StoredProcedureParameter(name = "linkApplId", type = String.class, mode = ParameterMode.IN) } ) }) Commented Mar 5, 2019 at 17:03

1 Answer 1

Reset to default
1

Finally, this SO link (How to call Oracle Function or Procedure using Hibernate (EntityManager) or JPA 2) and this article (https://vladmihalcea.com/how-to-call-oracle-stored-procedures-and-functions-from-hibernate/) helped me resolve my issue. Changes I did to get things working.

LinkAppl.java

@Entity
@Table(name = "LINK_APPL")
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;
}

LinkApplService.java

@Service
@Log4j2
public class LinkApplService {

    private static final String CHECK_ACTIVE_APPL_QUERY = "select PKG_USROUTER.CHECK_ACTIVE_APPL(:appl) from DUAL";

    @PersistenceContext
    private EntityManager em;
    @Autowired
    private LinkApplRepository linkApplRepository;

    public String checkApplicationActive(String applicationId) {
        String activeFlag = (String) em.createNativeQuery(CHECK_ACTIVE_APPL_QUERY)
                .setParameter("appl", applicationId)
                .getSingleResult();
        log.debug("activeFlag: " + activeFlag);
        return activeFlag;
    }

}

LinkApplController.java

@Controller
@RequestMapping("/applications")
@Log4j2
public class LinkApplController {

    @Autowired
    private LinkApplService linkApplService;

    @GetMapping
    public String getAllApplLinks(Model model) {
        String activeFlag = linkApplService.checkApplicationActive("TITAN");
        log.debug("@@@@@@@@@@@@@@@@activeFlag: " + activeFlag);
        model.addAttribute("activeFlag", activeFlag);    
        return "index";
    }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.