Issue parsing Oracle SQL BLOB type (ERROR: Invalid column type: getCLOB not implemented for class oracle.jdbc.driver.T4CLongAccessor)

[Benjamin Pusey]

Versions

ojdbc7 : 12.1.0.2,

          mybatis: 3.2.3,

mybatis-spring: 1.2.1

Issue Description

I am attempting to create a generic CRUD Interface. I am currently building out the API endpoint for Read with mybatis.

I have built a test table containing every Oracle Sql Data type to make sure my api can successfully handle any sql table given to it.

The API works perfectly for most of the simple types like VARCHAR and NUMBER but is currently failing on the BLOB column. The stack trace and my mapper code is below.

My mapper calls a select * from an owner.tableName owner and table name are provided via arguments.

NOTE: As this is a generic CRUD Interface I am not and cannot use any sort of resultMap.

I will not know what data and dataTypes are being sent back. I believe MyBatis can figure

this out for me and return a hashMap of corresponding Java objects. Please correct me if I'm wrong.

Mapper.xml

<mapper namespace="CRUD.read.mapper.ReadDataMapper">

<select id="readDataFromTable" resultType="HashMap">

SELECT * FROM ${owner}.${tableOrViewName}

</select>

</mapper>

Interface that defines ReadDataMapper.

Mapper.java

public interface ReadDataMapper {

public List<Map<String, Object>> readDataFromTable(@Param("owner") String owner, @Param("tableOrViewName") String tableOrViewName);

}

Below is the stack trace I highlighted what I believe to be the crucial parts of the trace.

Stack Trace

SEVERE: Servlet.service() for servlet [jersey-servlet] in context with path [/CRUD] threw exception [org.springframework.jdbc.UncategorizedSQLException: 

### Error querying database.  Cause: java.sql.SQLException: Invalid column type: getCLOB not implemented for class oracle.jdbc.driver.T4CLongAccessor

### The error may exist in file [C:\Users\A592468\Dev\CRUD\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\wtpwebapps\CRUD\WEB-INF\classes\CRUD\read\mapper\ReadDataMapper.xml]

### The error may involve defaultParameterMap

### The error occurred while setting parameters

### SQL: SELECT * FROM B_ONE_OPS.CRUD_TEST_ORACLE_DATATYPES

### Cause: java.sql.SQLException: Invalid column type: getCLOB not implemented for class oracle.jdbc.driver.T4CLongAccessor

; uncategorized SQLException for SQL []; SQL state [99999]; error code [17004]; Invalid column type: getCLOB not implemented for class oracle.jdbc.driver.T4CLongAccessor; nested exception is java.sql.SQLException: Invalid column type: getCLOB not implemented for class oracle.jdbc.driver.T4CLongAccessor] with root cause

java.sql.SQLException: Invalid column type: getCLOB not implemented for class oracle.jdbc.driver.T4CLongAccessor

at oracle.jdbc.driver.GeneratedAccessor.getCLOB(GeneratedAccessor.java:1064)

at oracle.jdbc.driver.GeneratedStatement.getCLOB(GeneratedStatement.java:448)

at oracle.jdbc.driver.GeneratedScrollableResultSet.getCLOB(GeneratedScrollableResultSet.java:1455)

at oracle.jdbc.driver.GeneratedScrollableResultSet.getClob(GeneratedScrollableResultSet.java:339)

at oracle.jdbc.driver.GeneratedResultSet.getClob(GeneratedResultSet.java:1295)

at org.apache.ibatis.type.ClobTypeHandler.getNullableResult(ClobTypeHandler.java:38)

at org.apache.ibatis.type.ClobTypeHandler.getNullableResult(ClobTypeHandler.java:25)

at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:51)

at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applyAutomaticMappings(DefaultResultSetHandler.java:415)

at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:334)

at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForSimpleResultMap(DefaultResultSetHandler.java:289)

at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:264)

at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:234)

at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:152)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:57)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:70)

at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:57)

at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:259)

at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:132)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:98)

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.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358)

at com.sun.proxy.$Proxy14.selectList(Unknown Source)

at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:198)

at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:114)

at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58)

at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)

at com.sun.proxy.$Proxy17.readDataFromTable(Unknown Source)

at CRUD.read.dao.ReadDao.readDataFromTable(ReadDao.java:18)

at CRUD.read.ReadService.getTables(ReadService.java:27)

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.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)

at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:151)

at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:171)

at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$TypeOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:195)

at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:104)

at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:367)

at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:349)

at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:106)

at org.glassfish.jersey.server.ServerRuntime$1.run(ServerRuntime.java:259)

at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)

at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)

at org.glassfish.jersey.internal.Errors.process(Errors.java:315)

at org.glassfish.jersey.internal.Errors.process(Errors.java:297)

at org.glassfish.jersey.internal.Errors.process(Errors.java:267)

at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:318)

at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:236)

at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:983)

at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:361)

at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:372)

at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:335)

at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:218)

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

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

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

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

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

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

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

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)

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

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

at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:958)

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

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

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

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

at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)

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

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

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

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

Does anyone know how to solve this issue?

[Iwao AVE!]

Hi,

The error implies that the column is actually defined as LONG type in the database.

If that's the case, you should change the column type to BLOB.
Oracle says:

> Do not create tables with LONG columns. Use LOB columns (CLOB, NCLOB, BLOB) instead.

> LONG columns are supported only for backward compatibility.

https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF00201

If it's not possible to change the column type, you may need to map an appropriate type handler to LONGVARCHAR in the config.

<typeHandlers>

  <typeHandler

    jdbcType="LONGVARCHAR"

    handler="org.apache.ibatis.type.BlobTypeHandler"/>

</typeHandlers>

Regards,

Iwao

--


You received this message because you are subscribed to the Google Groups "mybatis-user" group.


To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.


For more options, visit https://groups.google.com/d/optout.

[Benjamin Pusey]

Ahhhh that makes sense. I definetly have a Long column in my test table as I'm trying to make sure my API can handle all of oracles datatypes even the out of date ones. I misunderstood the issue. The Lob columns I have must be working fine it's just the outdated LONG column that's not supported. I will try the typeHandler fix! You've been extremely helpful thank you.