File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes Eclipselink, oracle 11 and clobs Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Eclipselink, oracle 11 and clobs" Watch "Eclipselink, oracle 11 and clobs" New topic
Author

Eclipselink, oracle 11 and clobs

damien malone
Ranch Hand

Joined: May 06, 2003
Posts: 35
Hi,

Using eclipselink to access oracle 11g database. Have a clob in one table, and it all works fine where the contents of the clob is less than 32676 characters long, but, when its longer, I get the following error

Caused by: java.sql.SQLException: ORA-00600: internal error code, arguments: [kpolcbLobRead1: invalid len & bufp], [], [], [], [], [], [], [], [], [], [], []

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:283)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:278)
at oracle.jdbc.driver.T4C8TTILob.receiveReply(T4C8TTILob.java:930)
at oracle.jdbc.driver.T4C8TTIClob.read(T4C8TTIClob.java:223)
at oracle.jdbc.driver.T4CConnection.getChars(T4CConnection.java:2625)
at oracle.sql.CLOB.getChars(CLOB.java:354)
at oracle.sql.CLOB.getSubString(CLOB.java:247)
at org.eclipse.persistence.internal.helper.ConversionManager.convertObjectToString(ConversionManager.java:675)

My attribute is defined as follows

@Lob
@Column(name="MVH_XML_CONTENT")
private String mvhXmlContent;

Any ideas would be great.

Thanks
Damien
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
That is odd, what version of Oracle JDBC and EclipseLink are you using? What type of connection pooling are you using?
Can you include the full exception stack trace.

The only thing I can think of is either you lost the connection that read the clob, or the driver has a 32k limitation.

You could try using an exclusive connection in EclipseLink to see if it is a connection issue, ("eclipselink.jdbc.exclusive-connection.mode"="Always", in persistence.xml)

Or try enabling stream binding, (session.getLogin().useStreamsForBinding(), using SessionCustomizer).


TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
damien malone
Ranch Hand

Joined: May 06, 2003
Posts: 35
Hi James

Thanks for your reply, I've tried that but still the same error. I am currently using Oracle JDBC driver Version: 10.2.0.4.0 and eclipselink 1.2.0 (this was taken from the toplink distribution so oracle will support (also have a TAR open on this)). I have tried with the latest eclipselink version to see if the error is in there but get the same issue. I've attached the full console output. In this example, I am just using a straight JDBC connection to test and recreate as this error is happening in one of our live batch jobs.

The underlying data is stored in an XMLType row, but we are pulling it back from a view that has the clob contents


CREATE OR REPLACE FORCE VIEW "MVH_DATA"."V_MVH_XML_DOC" ("MVH_DOC_ID", "MVH_XML_CONTENT", "MVH_CREATE_DATE", "MVH_CREATE_BY", "MVH_UPDATE_DATE", "MVH_UPDATE_BY") AS
select mvh_doc_id,
xml_doc.mvh_xml_content.getClobval() as mvh_xml_content,
mvh_create_date,
mvh_create_by,
mvh_update_date,
mvh_update_by
from mvh_xml_doc xml_doc;

and the test class just loads up the instance of the class using the generic eclipselink load. I have also tried to load directly from the table with a DirectToXMLTypeMapping descriptor customiser but get the same issue.

Any help on this would be gratefully appreciated

Thanks
Damien

[Edit - our firewall wont let me upload the stack trace attachment so I have to insert it in here, sorry for this]



[EL Finest]: 2011-05-03 12:11:11.328--UnitOfWork(33114655)--Thread(Thread[main,5,main])--Execute query ReadObjectQuery(name="readObject" referenceClass=XmlDoc sql="SELECT MVH_DOC_ID, MVH_CREATE_BY, MVH_CREATE_DATE, MVH_UPDATE_BY, MVH_UPDATE_DATE, MVH_XML_CONTENT FROM V_MVH_XML_DOC WHERE (MVH_DOC_ID = ?)")
[EL Finest]: 2011-05-03 12:11:11.328--ServerSession(28497887)--Connection(5024475)--Thread(Thread[main,5,main])--Connection acquired from connection pool [default].
[EL Finest]: 2011-05-03 12:11:11.328--ClientSession(3870732)--Thread(Thread[main,5,main])--reconnecting to external connection pool
[EL Fine]: 2011-05-03 12:11:11.328--ClientSession(3870732)--Connection(23406754)--Thread(Thread[main,5,main])--SELECT MVH_DOC_ID, MVH_CREATE_BY, MVH_CREATE_DATE, MVH_UPDATE_BY, MVH_UPDATE_DATE, MVH_XML_CONTENT FROM V_MVH_XML_DOC WHERE (MVH_DOC_ID = ?)
bind => [214815]
[EL Warning]: 2011-05-03 12:11:13.032--UnitOfWork(33114655)--Thread(Thread[main,5,main])--Local Exception Stack:
Exception [EclipseLink-3001] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.ConversionException
Exception Description: The object [oracle.sql.CLOB@1510d96], of class [class oracle.sql.CLOB], could not be converted to [class java.lang.String].
Internal Exception: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-00600: internal error code, arguments: [kpolcbLobRead1: invalid len & bufp], [], [], [], [], [], [], [], [], [], [], []

Error Code: 600
at org.eclipse.persistence.exceptions.ConversionException.couldNotBeConverted(ConversionException.java:79)
at org.eclipse.persistence.internal.helper.ConversionManager.convertObject(ConversionManager.java:146)
at org.eclipse.persistence.internal.databaseaccess.DatasourcePlatform.convertObject(DatasourcePlatform.java:157)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.getObject(DatabaseAccessor.java:1146)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.fetchRow(DatabaseAccessor.java:927)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:625)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:526)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1729)
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:234)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectOneRow(DatasourceCallQueryMechanism.java:667)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectOneRowFromTable(ExpressionQueryMechanism.java:2603)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectOneRow(ExpressionQueryMechanism.java:2574)
at org.eclipse.persistence.queries.ReadObjectQuery.executeObjectLevelReadQuery(ReadObjectQuery.java:444)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1080)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:808)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1040)
at org.eclipse.persistence.queries.ReadObjectQuery.execute(ReadObjectQuery.java:412)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1126)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2842)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1521)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1503)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1463)
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.executeQuery(EntityManagerImpl.java:781)
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.findInternal(EntityManagerImpl.java:725)
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.find(EntityManagerImpl.java:619)
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.find(EntityManagerImpl.java:498)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:365)
at $Proxy20.find(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:240)
at $Proxy20.find(Unknown Source)
at ie.vhi.mvh.dao.jpa.GenericDaoImpl.load(GenericDaoImpl.java:30)
at ie.vhi.mvh.test.testXMLDoc.testDao(testXMLDoc.java:32)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:82)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:240)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:180)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:49)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-00600: internal error code, arguments: [kpolcbLobRead1: invalid len & bufp], [], [], [], [], [], [], [], [], [], [], []

Error Code: 600
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:309)
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:305)
at org.eclipse.persistence.internal.helper.ConversionManager.convertObjectToString(ConversionManager.java:677)
at org.eclipse.persistence.internal.helper.ConversionManager.convertObject(ConversionManager.java:99)
... 71 more
Caused by: java.sql.SQLException: ORA-00600: internal error code, arguments: [kpolcbLobRead1: invalid len & bufp], [], [], [], [], [], [], [], [], [], [], []

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:283)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:278)
at oracle.jdbc.driver.T4C8TTILob.receiveReply(T4C8TTILob.java:930)
at oracle.jdbc.driver.T4C8TTIClob.read(T4C8TTIClob.java:223)
at oracle.jdbc.driver.T4CConnection.getChars(T4CConnection.java:2625)
at oracle.sql.CLOB.getChars(CLOB.java:354)
at oracle.sql.CLOB.getSubString(CLOB.java:247)
at org.eclipse.persistence.internal.helper.ConversionManager.convertObjectToString(ConversionManager.java:675)
... 72 more

[EL Finer]: 2011-05-03 12:11:13.079--UnitOfWork(33114655)--Thread(Thread[main,5,main])--release unit of work
[EL Finest]: 2011-05-03 12:11:13.079--ServerSession(28497887)--Connection(23406754)--Thread(Thread[main,5,main])--Connection released to connection pool [default].

James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
Why are you using a 10.2 JDBC driver with an 11g database?

Can you try the latest JDBC driver version, does the error still occur?

Are you using the thin or OCI driver? Try using the OCI driver, does the error still occur?

Can you try a normal CLOB column, instead of a view on an XMLType, does the error still occur?

Can you try a BLOB column, does the error still occur?

Clob also defines a stream API instead of getSubString(). Can you try a direct JDBC test calling the stream API, does it work?
damien malone
Ranch Hand

Joined: May 06, 2003
Posts: 35
Hi James,

I'm not sure why we are using the old drivers, I have just moved onto this project, but will get them updated.

I updated the drivers locally, and it didnt fix the issue, so I downloaded the oci dll and tried oci, still no joy. So, instead of using the view, I created a temporary table with the same SQL as the view,

CREATE table MVH_XML_DOC_temp ("MVH_DOC_ID", "MVH_XML_CONTENT", "MVH_CREATE_DATE", "MVH_CREATE_BY", "MVH_UPDATE_DATE", "MVH_UPDATE_BY") AS
select mvh_doc_id,
xml_doc.mvh_xml_content.getClobval() as mvh_xml_content,
mvh_create_date,
mvh_create_by,
mvh_update_date,
mvh_update_by
from mvh_xml_doc xml_doc;

and hey presto, it worked, so problem is on the database side, I have no problem what so ever is reading a CLOB > 32k when clob is the native type. For now, easiest solution to go change the datatype on the table.

Thanks very much for your help

Damien.
Henny Beyeler
Greenhorn

Joined: Nov 10, 2011
Posts: 2
Hi Damien

Have you created a Service Request at Oracle? We have the same problem as you have, but I could not find anything yet at Oracle like Bug or Info.

Have you more information for me concerning this problem, since you posted this in April?

Kind regards
Henny
damien malone
Ranch Hand

Joined: May 06, 2003
Posts: 35
Hi Henny

Yeah, we opened up an oracle TAR, they recommended checking the version of the jar file with your drivers, setting the buffer size through SQL (could only manage this in SQL developer), and a few other things I'm sorry I cant remember, in the end, none of it worked and we ended up going back to a clob. We just cast the column to xmltype whenever we want to run xpath queries on it then

i.e. select extractvalue(xmltype(clob_column_name), 'xpath_expression') from your_table_name

This meant adding a new column to the table of type clob and inserting the xmltype column values into that. This operation took a couple of hours on our live system which was a bit of a pain, and we then dropped the xmltype column.

Hope this helps, best of luck
Henny Beyeler
Greenhorn

Joined: Nov 10, 2011
Posts: 2
Hi Damien

Thanks for your very quick answer! Can you provide me with your SR-number? I think I try making some pressure at Oracle. The change of Oracle from 11.2.0.1 with XMLType default CLOB to 11.2.0.2 with XMLType default Binary Storage has given us allready a lot of Problems (f.ex. the length calculation is not correct anymore, problems with imports into other Charactersets etc.).

This is just something more... ;-(

Kind regards
Henny
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Eclipselink, oracle 11 and clobs