user5660423
Reputation: 21
SQLRecoverableException: No more data to read from socket only occurs when invoking an oracle package
The following error only occurs when invoking an oracle package. Hundreds of different functions are invoked without any problem, but sometimes (not always) when this package is invoked the following exception occurs.
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)
The oracle package:
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's StoredProcedure class is used for invocation of functions. Access is via a webservice REST (RestController) with BASIC authentication.
The jdbc driver used is ojdbc7.jar than the official version for Oracle 12 and Java 7.
Java code
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();
}
}
}
Upvotes: 1
Views: 18361
Answers (1)
Related Questions
- No more data to read from socket error
- SQLRecoverableException: I/O Exception: Connection reset
- java.sql.SQLException: Io exception: Socket read timed out vs Closed Connection
- java.sql.SQLRecoverableException:Closed Connection
- Oracle Autonomous database JDBC Error No more data to read from socket but webapp works without errors
- java.sql.SQLRecoverableException: No more data to read from socket reading from NoSQL
- Oracle JDBC No more data to read from socket on RedHat 7.7
- java.sql.SQLRecoverableException - reconnect from jdbc
- Oracle JDBC pool SocketException: Connection reset on startup
- Connection reset by peer by oracle on processing of java application