clob 데이타 처리

CLOB 데이타를 다루는 과정에서 문제가 발생하였다.

상황은 다음과 같다.

A 시스템의 데이타를 B 시스템으로 REST SERVICE를 통해서 가져오는 과정에서

A 시스템의 CLOB DATA를 B 시스템에서 저장하는 경우 문제가 발생하였다.

게시판 내용인데

COMTNBBS.NTT_CN이다.

글자수의 길이 때문이라고 생각을 해서 검색해봤더니 

드라이버 버전 이슈가 많았다.

B 시스템의 드라이버는 내가 직업 ojdbc6으로 넣어서 아닌 것 같았다.

A 시스템에서 데이타를 저장하는 경우 CLOB 데이타가 잘 저장이 되는 걸 알고 있었고,

소스상에도 별다은 내용은 없어서

B 시스템에서 문제가 없을 거라고 생각이 되어서 더욱 시간이 걸린 것 같다.

몇일간의 고민과 테스트 끝에... 의외로 쉽게 해결이 되었다.

한참전에 개발할 때 CLOB 처리를 할 때 

CLOB 필드를 다룰 때는 EMPTY_CLOB()을 써준 다음에 UPDATE 를 통해서 CLOB 데이타를 넣어줬었는데.

혹시나 하는 마음으로 적용해 봤더니 빙고!!!!

즉 

INSERT INTO 테이블( A, B, C) VALUES('A', 'B', EMPTY_CLOB());

UPDATE 테이블  SET C =데이타  WHERE.....

UPDATE의 경우에도 먼저 EMPTY_CLOB 을 하고 다시 데이타를 UPDATE 하는 방식으로 하니까 잘 된다.

전자정부프레임워크의 경우에  lobHandler 때문인지 단순히 String 형태로 잘 처리되는 것으로 알고 있었는데

예외의 경우도 있나보다 하는 생각을 하게 되었다. 

참고로 A 나 B 시스템 내부에서는 CLOB 을 위와 같이 하지 않아도 저장/수정이 잘 된다.

다만 타 시스템의 CLOB 데이타를 가져오는 경우에만 발생하는 현상이다.

문자열을 4000자 이내로 잘라서 넣어봐도 

ORA-01461: can bind a LONG value only for insert into a LONG column

ORA-01461: LONG 값은 LONG 열에 삽입할 때만 바인드할 수 있습니다.

이런 오류도 발생하였었다.

** CLOB 이 안될경우 먼저 위방법부터 해본다음에 다른 방법을 강구해보자 !!!!라고 생각을 하게 되었다.

** 주의**

UPDATE 

테이블명

SET 

필드명=#필드명#

WHERE USER_ID = 서브쿼리로 구해온 사용자 아이디

  AND DEL_YN = 'N'

처럼 해도 동일한 오류가 발생한다.

UPDATE 

테이블명

SET 

필드명=#필드명#

WHERE USER_ID = #userId#

  AND DEL_YN = 'N'

의 형태로만 해야 하는 것 같다. 논리적으로 설명은 안되지만.. 테스트 결과가 그렇다.

String으로 받아서 CLOB =#nttCn# 으로 넣을려고 하면 아래와 같은 오류 발생한다.

--- Cause: java.lang.reflect.UndeclaredThrowableException

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

at egovframework.rte.psl.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:206)

at egovframework.rte.psl.orm.ibatis.SqlMapClientTemplate.insert(SqlMapClientTemplate.java:367)

at egovframework.rte.psl.dataaccess.EgovAbstractDAO.insert(EgovAbstractDAO.java:92)

at egovframework.sj23.pot.biz.com.blog.service.impl.AaaDAO.insertAaaaaaaa(AaaDAO.java:24)

at egovframework.sj23.pot.biz.com.blog.service.impl.BlogServiceImpl.insertAaaaaaaa(BlogServiceImpl.java:148)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)

at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)

at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:267)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)

at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:58)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)

at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)

at com.sun.proxy.$Proxy43.insertAaaaaaaa(Unknown Source)

at egovframework.sj23.pot.biz.com.blog.web.BlogSchController.insertAaaaaaaa(BlogSchController.java:50)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215)

at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)

at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:781)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:721)

at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:943)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:877)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966)

at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:857)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)

at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)

at egovframework.sj23.sec.security.filter.EgovSpringSecurityLoginFilter.doFilter(EgovSpringSecurityLoginFilter.java:244)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)

at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)

at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:108)

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.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.ui.DefaultLoginPageGeneratingFilter.doFilter(DefaultLoginPageGeneratingFilter.java:155)

at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)

at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:199)

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.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.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:125)

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.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)

at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)

at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:344)

at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:261)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)

at egovframework.sj23.cmm.util.EgovUrlRewriteFilter.doFilter(EgovUrlRewriteFilter.java:133)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)

at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502)

at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1132)

at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1539)

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1495)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

at java.lang.Thread.run(Thread.java:745)

Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:   

--- The error occurred in egovframework/sqlmap/sj23/pot/Blog.xml.  

--- The error occurred while applying a parameter map.  

--- Check the AaaDAO.insertAaaaaaaa-InlineParameterMap.  

--- Check the statement (update failed).  

--- Cause: java.lang.reflect.UndeclaredThrowableException

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:110)

at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDelegate.java:393)

at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.java:82)

at egovframework.rte.psl.orm.ibatis.SqlMapClientTemplate$8.doInSqlMapClient(SqlMapClientTemplate.java:369)

at egovframework.rte.psl.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:203)

... 105 more

Caused by: java.lang.reflect.UndeclaredThrowableException

at com.sun.proxy.$Proxy29.close(Unknown Source)

at org.apache.commons.dbcp.DelegatingStatement.close(DelegatingStatement.java:168)

at org.apache.commons.dbcp.DelegatingStatement.close(DelegatingStatement.java:168)

at com.ibatis.sqlmap.engine.execution.SqlExecutor.closeStatement(SqlExecutor.java:518)

at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:83)

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteUpdate(MappedStatement.java:216)

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:94)

... 109 more

Caused by: java.lang.reflect.InvocationTargetException

at sun.reflect.GeneratedMethodAccessor76.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at core.log.aop.handler.DaoInfo.doAround(DaoInfo.java:95)

at core.log.aop.reflection.profiler.AroundProfiler.invoke(AroundProfiler.java:19)

... 116 more

Caused by: java.sql.SQLRecoverableException: 접속 종료

at oracle.jdbc.driver.PhysicalConnection.needLine(PhysicalConnection.java:5389)

at oracle.jdbc.driver.OracleStatement.closeOrCache(OracleStatement.java:1578)

at oracle.jdbc.driver.OracleStatement.close(OracleStatement.java:1563)

at oracle.jdbc.driver.OracleStatementWrapper.close(OracleStatementWrapper.java:94)

at oracle.jdbc.driver.OraclePreparedStatementWrapper.close(OraclePreparedStatementWrapper.java:80)

at core.log.impl.PreparedStatementLoggable.close(PreparedStatementLoggable.java:373)

... 121 more

[2018-09-22 10:44:28] [ERROR]『oracle.jdbc.driver.PhysicalConnection:needLine(5389)』

이 외에도 이런저런 처리를 시도하면서

missing CR 오류도 발생