spring SQL可恢复异常错误：只有在调用oracle程序包时，才会出现没有更多数据可从套接字读取的情况

s5a0g9ez  于 2022-11-21  发布在  Spring
关注(0)|答案(1)|浏览(65)

以下错误仅在调用Oracle程序包时出现。调用数百个不同的函数时不会出现任何问题，但有时（并非始终）在调用此程序包时会出现以下异常错误。

due to exception [CallableStatementCallback; SQL [{? = call RCREDITO.PQCRESLG0026.FNRECALCULO(?, ?, ?, ?, ?, ?)}]; No more data to read from socket; nested exception is java.sql.SQLRecoverableException: No more data to read from socket]: org.springframework.dao.RecoverableDataAccessException: CallableStatementCallback; SQL [{? = call RCREDITO.PQCRESLG0026.FNRECALCULO(?, ?, ?, ?, ?, ?)}]; No more data to read from socket; nested exception is java.sql.SQLRecoverableException: No more data to read from socket
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:154)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:201)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:110)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:50)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:246)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:87)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:246)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:246)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:121)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:246)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
at org.springframework.boot.context.web.ErrorPageFilter.doFilter(ErrorPageFilter.java:120)
at org.springframework.boot.context.web.ErrorPageFilter.access$000(ErrorPageFilter.java:61)
at org.springframework.boot.context.web.ErrorPageFilter$1.doFilterInternal(ErrorPageFilter.java:95)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.boot.context.web.ErrorPageFilter.doFilter(ErrorPageFilter.java:113)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:246)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:231)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:149)
at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:169)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:150)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:97)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:102)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:854)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:653)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:511)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
at org.jboss.threads.JBossThread.run(JBossThread.java:122)
Caused by: java.sql.SQLRecoverableException: No more data to read from socket
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1142)
at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1099)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:288)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
at oracle.jdbc.driver.T4CStatement.fetch(T4CStatement.java:1073)
at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:359)
at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:263)
at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:92)
at org.springframework.jdbc.core.JdbcTemplate.processResultSet(JdbcTemplate.java:1321)
at org.springframework.jdbc.core.JdbcTemplate.extractOutputParameters(JdbcTemplate.java:1279)
at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1186)
at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1173)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1121)
... 154 more
WARN  [org.jboss.jca.core.connectionmanager.listener.TxConnectionListener] (ConnectionValidator) IJ000305: Connection error occured: org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@57aeca52[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@584799b0 connection handles=0 lastUse=1491082422691 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@44bb1f17 pool internal context=SemaphoreArrayListManagedConnectionPool@58f64241[pool=jdbc/SCL] xaResource=LocalXAResourceImpl@f77e708[connectionListener=57aeca52 connectionManager=5f5cabf6 warned=false currentXid=null productName=Oracle productVersion=Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options jndiName=java:/jdbc/SCL] txSync=null]: java.sql.SQLRecoverableException: No more data to read from socket
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1142)
at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1099)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:288)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:852)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1890)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1855)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:304)
at org.jboss.jca.adapters.jdbc.CheckValidConnectionSQL.isValidConnection(CheckValidConnectionSQL.java:74)
at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnectionFactory.isValidConnection(BaseWrapperManagedConnectionFactory.java:1100)
at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.checkValid(BaseWrapperManagedConnection.java:513)
at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnectionFactory.getInvalidConnections(BaseWrapperManagedConnectionFactory.java:921)
at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.validateConnections(SemaphoreArrayListManagedConnectionPool.java:987)
at org.jboss.jca.core.connectionmanager.pool.validator.ConnectionValidator$ConnectionValidatorRunner.run(ConnectionValidator.java:277)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)

Oracle软件包：

FUNCTION  FNCATALOGO (PA_FIPAISID        RCREDITO.CREDCLIENTEUNICOPEDIDO.FIPAIS%TYPE,
                                                        PA_FICANAL         RCREDITO.CREDCLIENTEUNICOPEDIDO.FICANAL%TYPE,
                                                        PA_FISUCURSAL  RCREDITO.CREDCLIENTEUNICOPEDIDO.FISUCURSAL%TYPE,
                                                         PA_FIFOLIO          RCREDITO.CREDCLIENTEUNICOPEDIDO.FIFOLIO%TYPE)
                                        RETURN  RCREDITO.PATYPES.rcgCursor
  IS
     rcl_CursorSalida        RCREDITO.PATYPES.rcgCursor;
    csl_5                            PLS_INTEGER := 5;
   csl_n1                         NUMBER(1):=-1;
   csl_sysdate                DATE:=SYSDATE;
   csl_user                      VARCHAR2(100):=USER;
   VL_ATRASO              RCREDITO.CLASIFICACTEU.FIDIASATRASOMAX%TYPE;
   VL_DEPTO                 RCREDITO.CLASIFICACTEU.FIDEPTOID%TYPE;
   VL_TIPODEPTO        RCREDITO.CLASIFICACTEU.FIDEPTOID%TYPE;
   VL_TOTAL               NUMBER:=0;
    REGULARIZACIONES ARRAY_REGULARIZACIONES;
    vi number(3):=0;

      CURSOR CUR_REGULARIZACIONES IS (SELECT TPP.FIFOLIO,TPP.FIPLAZO
                                                                           FROM RCREDITO.TACPLANESPAGO  TPP
                                                                        WHERE TPP.FIFOLIO                =TPP.FIFOLIO+csl_0                            
                                                                              AND TPP.FIIDTIPOPLAN IN (CSL_1,CSL_2,CSL_3,CSL_4,CSL_5,CSL_9,CSL_10)
                                                                              AND TPP.FIPAIS               =TPP.FIPAIS+csl_0                            
                                                                              AND TPP.FIIDZONA        =TPP.FIIDZONA+csl_0                            
                                                                              AND TPP.FIREGIONAL     =TPP.FIREGIONAL+csl_0                            
                                                                              AND TPP.FIDEPTOID         =TPP.FIDEPTOID+csl_0
                                                                              AND VL_ATRASO BETWEEN FIDIASATRASMIN AND FIDIASATRASMAX
                                                                               AND FNSTATUS=CSL_1
                                                                               AND FNTIPOCONVENIO=CSL_2);                                     
  BEGIN

  /*OBTENEMOS EL DEPARTAMENTO Y DIAS DE ATRASO DEL CLIENTE*/
            SELECT FIDIASATRASOMAX,FIDEPTOID,FITIPODEPTO
                INTO VL_ATRASO,VL_DEPTO,VL_TIPODEPTO
               FROM RCREDITO.CLASIFICACTEU 
            WHERE FIPAIS           =PA_FIPAISID
                  AND FICANAL       =PA_FICANAL
                  AND FISUCURSAL=PA_FISUCURSAL
                  AND FIFOLIO       =PA_FIFOLIO;

  /*OBTENEMOS EL TOTAL DE PLANES QUE LE CORRESPONDEN AL CLIENTE PARA INICIALIZAR EL ARRAY */
SELECT COUNT(CSL_1)
   INTO VL_TOTAL
   FROM RCREDITO.TACPLANESPAGO  TPP
WHERE TPP.FIFOLIO                =TPP.FIFOLIO+csl_0                            
      AND TPP.FIIDTIPOPLAN IN (CSL_1,CSL_2,CSL_3,CSL_4,CSL_5,CSL_9,CSL_10)
      AND TPP.FIPAIS               =TPP.FIPAIS+csl_0                            
      AND TPP.FIIDZONA        =TPP.FIIDZONA+csl_0                            
       AND TPP.FIREGIONAL     =TPP.FIREGIONAL+csl_0                            
       AND TPP.FIDEPTOID         =TPP.FIDEPTOID+csl_0
       AND VL_ATRASO BETWEEN FIDIASATRASMIN AND FIDIASATRASMAX
       AND FNSTATUS=CSL_1
       AND FNTIPOCONVENIO=CSL_2;

/*Inicializamos el array de regularizaciones*/
  REGULARIZACIONES:= ARRAY_REGULARIZACIONES();
  REGULARIZACIONES.EXTEND(VL_TOTAL);

         /*POR CADA PLAN QUE APLIQUE PARA EL CLIENTE SE APLICAN LOS RESPECTIVOS CALCULOS*/   
            FOR CUR_TACPLANES IN CUR_REGULARIZACIONES
              LOOP
                        vi:=vi+csl_1;
                        REGULARIZACIONES(vi):=FNCALCULO(PA_FIPAISID,PA_FICANAL,PA_FISUCURSAL,PA_FIFOLIO,CUR_TACPLANES.FIFOLIO,csl_n1);
              END LOOP;

              OPEN rcl_CursorSalida FOR
               SELECT    FIPAISID                                                       AS FIPAIS,
                                FICANAL                                                         AS FICANAL ,
                                FISUCURSAL                                                  AS FISUCURSAL ,
                                FIFOLIO                                                          AS FIFOLIO,
                                FOLIOWEB                                                      AS FW ,
                                IDPLAN                                                            AS FIFOLIOPLAN,
                                PLAZO                                                              AS RPLAZO,
                               DIASPROMESA                                               AS fidiaspromesa ,
                               TIPOPP,
                                TIPOCONVENIO                                            AS RTIPOCONV,
                                TIPOPLAN                                                        AS RPLAN,
                                TIPODEPTO                                                     AS  FISEGMENTO,
                                PORENGANCHEMINIMO                               AS  FIENGANCHEMINIMO,
                                PAGOSUGERIDO ,
                                PAGOREQUERIDO ,
                                SALDOATRASADO                                         AS RSALDOATRASADO,
                                CAPITALATRASADO                                      AS RSALDOXDEVENGAR  ,
                                 INTERESDEVENGADO                                  AS  RINTERESATRASADO,
                                 MORATORIOS                                                 AS RMORATORIOS,
                                 DEVENGOSEMANAL                                       AS RABONOSEMANAL, 
                                 TOTALBONIFICACIONES                              AS RBONIFICACIONES,
                                  BONIFICACIONMORATORIOS                   AS RBONIFMORATORIO,
                                  PORBONIFICACIONMORATORIOS ,
                                  BONIFICACIONINTERESDEVENGADO    AS RBONIFINTERES,
                                  PORBONIFINTERESDEVENGADO               AS RPORCBONIFINTERES,
                                  PAGOPUNTUALPLAN                                     AS RPAGOPUNTUAL ,
                                  ATRASADOMASPP                                         AS RSALDOATRASADOCONBONIF ,
                                  DEVENGODURANTEPLAN                             AS RBONIFDEVENGOSEM,
                                   PAGOINICIALCALCULO ,
                                   PAGOSEMANALCALCULO                             AS PAGOSEMANALPLAN,
                                   PAGOSEMPUNTUALCALCULO                      AS RPAGOSEMPUNTUALPLAN,
                                   PAGOSEMANALATRASADOCALCULO        AS RPAGOSEMATRASADO,
                                   PAGOTOTALCALCULO,
                                   PAGOTOTALREONDEADO,
                                   PAGOINICIAL                                          AS RPAGOINICIAL ,
                                   PAGOSEMANAL,
                                   SALDOAPAGARATRASADO ,
                                   SALDOAPAGARPUNTUAL,
                                   SALDOAPAGAR                                         AS RSALDOXPAGAR,
                                  SALDOTOTALMORAS ,
                                  SALDOTOTAL                                            AS RFNSALDO,
                                 SALDOPORDEVENGAR ,
                                  CAPITALPORDEVENGAR ,
                                  INTERESPORDEVENGAR ,
                                  INTERESESPORDEVENGARPLAZO ,
                                  CAPITALPORDEVENGARPLAZO ,
                                  BONIFICACIONINTERESPORDEVENGAR ,
                                  TOTALBONIFICACIONES ,
                                  SALDOAPAGARMORATORIOS ,
                                  SALDODEVENGODURANTEPLAN ,
                                   SALDOCAPITALPORDEVENGAR
                 FROM TABLE(REGULARIZACIONES);
                 RETURN rcl_CursorSalida;
    EXCEPTION WHEN 
        OTHERS THEN
           RCREDITO.SPREGISTRAERROR (SYSDATE, SQLCODE, SQLERRM, 'PQCRESLG0029');
                   OPEN rcl_CursorSalida FOR
                                    SELECT   csl_0                                                   AS FIIDMENSAJE,
                                                    'Ocurrio un error en la aplicacion' AS FCMENSAJE
                                   FROM DUAL;
                      RETURN rcl_CursorSalida;
  END FNCATALOGO;

Spring的StoredProcedure类用于调用函数。访问是通过带有BASIC身份验证的Web服务REST（RestController）进行的。
使用的jdbc驱动程序是ojdbc7.jar，而不是Oracle 12和Java 7的正式版本。
Java程式码

private class Function extends StoredProcedure {

    @SuppressWarnings("unchecked")
    public List execWithReturnCursor(String functionName, Map<String,Object> inputs) {

        logger.info("INICIANDO EJECUCION DE FUNCION");

        setDataSource(dataSource);
        setFunction(true);
        setSql(functionName);

        if(getOutput() == null && getMapper() != null) {
            super.declareParameter(new SqlOutParameter("cursor", OracleTypes.CURSOR, getMapper()));
        }
        else {
            super.declareParameter(new SqlOutParameter("cursor", OracleTypes.CURSOR, new BeanPropertyRowMapper(getOutput())));
        }

        for (Map.Entry<String,Object> entry : inputs.entrySet()) {
            Object value = inputs.get(entry.getKey());
            LOGGER.debug("Parametro {} ",entry.getKey());
            LOGGER.debug("Valor {} ",value);
            super.declareParameter(new SqlParameter(entry.getKey(), OracleStoreProcedure.getType(value)));
        }

        compile();
        String fn = functionName+"("+inputs+")";

        //LOGGER.info(fn);
        try {
            Map resultado = super.execute(inputs);
            return (List) resultado.get("cursor");
        }
        catch(Throwable seq){
            logger.info("Ocurrio un problema al invocar la funcion{}", seq);
            return new ArrayList();
        }
    }
}
spring

1条答案

按热度按时间
f87krz0w

f87krz0w1#

检查此question：它看起来和你的很相似。它说一个可能的解决方案可以是

  • 升级Oracle或
  • 设置oracle参数optim_peek_user_binds =假
赞(0）分享  回复(0）举报  2022-11-21
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com