Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Using CLOBs in entities with Oracle
Using CLOBs in entities with Oracle [message #702933] Wed, 27 July 2011 00:39 Go to next message
Brendan Haverlock is currently offline Brendan HaverlockFriend
Messages: 46
Registered: July 2009
Member
Hi all,

I am running into an issue with trying to use CLOBs in Oracle with EclipseLink. Basically, when I try to run a filter using the expression builder, I get the following error:

Error: ORA-00932: inconsistent datatypes: expected - got CLOB

I feel like I have tried everything. I have added the Oracle target database, I have upgraded my driver, updated EclipseLink, etc. and I am at my wits end. My column in my entity is annotated as follows:

    @Lob
    @Column(name = "description")
    private String description;


Can someone please help me with this?

Thanks a bunch!

Brendan

Report message to a moderator

Re: Using CLOBs in entities with Oracle [message #703484 is a reply to message #702933] Wed, 27 July 2011 16:08 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1389
Registered: July 2009
Senior Member
Hello,

What is the query you are executing and what is the generated SQL? Turn on logging to fine or higher to get the SQL logged, ie property:
<property name="eclipselink.logging.level" value="FINE"/>

Oracle will throw the exception if distinct is used when a clob is in the select statement, so check that your query isn't performing a join or other function that would require distinct be used.

Best Regards,
Chris

Report message to a moderator

Re: Using CLOBs in entities with Oracle [message #703539 is a reply to message #703484] Wed, 27 July 2011 17:12 Go to previous messageGo to next message
Brendan Haverlock is currently offline Brendan HaverlockFriend
Messages: 46
Registered: July 2009
Member
This is the query that is being generated:

SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum  FROM (SELECT t0.clob AS clob FROM code t0, 
    org t1 WHERE ((((((((UPPER(t0.x) = 'CJO0UUCO7G') AND (UPPER(t0.y) = 'FEODUQUUJH')) AND (t0.clob = '9BSMsjkuGA')) AND (UPPER(t0.a) = 'ZMP4ADKINK')) AND (UPPER(t0.b) = 'KOAFL4SNDN')) 
    AND (t0.c = 7)) AND (t1.d = 1021)) AND (t1.e = t0.e))) a WHERE ROWNUM <= 100) WHERE rnum > 0;

[Updated on: Wed, 27 July 2011 17:13]

Report message to a moderator

(no subject) [message #703564 is a reply to message #703484] Wed, 27 July 2011 17:12 Go to previous messageGo to next message
Brendan Haverlock is currently offline Brendan HaverlockFriend
Messages: 46
Registered: July 2009
Member
This is the query that is being generated:

SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum FROM (SELECT t0.description AS description4 FROM code t0,
org t1 WHERE ((((((((UPPER(t0.x) = 'CJO0UUCO7G') AND (UPPER(t0.y) = 'FEODUQUUJH')) AND (t0.clob = '9BSMsjkuGA')) AND (UPPER(t0.a) = 'ZMP4ADKINK')) AND (UPPER(t0.b) = 'KOAFL4SNDN'))
AND (t0.c = 7)) AND (t1.d = 1021)) AND (t1.e = t0.e))) a WHERE ROWNUM <= 100) WHERE rnum > 0;

Report message to a moderator

Re: (no subject) [message #704352 is a reply to message #703539] Thu, 28 July 2011 14:23 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member
Can you include the full exception stack trace.

James : Wiki : Book : Blog : Twitter

Report message to a moderator

(no subject) [message #704366 is a reply to message #703539] Thu, 28 July 2011 14:23 Go to previous messageGo to next message
James is currently offline JamesFriend
Messages: 272
Registered: July 2009
Senior Member
Can you include the full exception stack trace.
--
James : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/

Report message to a moderator

Re: (no subject) [message #704472 is a reply to message #704352] Thu, 28 July 2011 17:11 Go to previous messageGo to next message
Brendan Haverlock is currently offline Brendan HaverlockFriend
Messages: 46
Registered: July 2009
Member
 
Error Code: 932
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:644)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:535)
	at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1702)
	at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:566)
	at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:207)
	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.executeSelectCall(DatasourceCallQueryMechanism.java:264)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:646)
	at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2592)
	at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2551)
	at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:418)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1097)
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:829)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1056)
	at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:390)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1144)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2863)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1501)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1483)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1457)
	at org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:485)
	at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getResultList(EJBQueryImpl.java:742)
	at com.mirth.kana.filters.ExpressionBuilderFilter.getElements(ExpressionBuilderFilter.java:156)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:931)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:607)
	... 138 more


Seems to me like there needs to be a way for eclipselink to emit dbms_lob.instr(clob,'string') instead of doing a clob = 'string' in the where clause. That would fix this exception. Any thoughts now?

Thanks.

Report message to a moderator

(no subject) [message #704490 is a reply to message #704352] Thu, 28 July 2011 17:11 Go to previous messageGo to next message
Brendan Haverlock is currently offline Brendan HaverlockFriend
Messages: 46
Registered: July 2009
Member
Error Code: 932
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:644)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:535)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1702)
at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:566)
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:207)
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.executeSelectCall(DatasourceCallQueryMechanism.java:264)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:646)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2592)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2551)
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:418)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1097)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:829)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1056)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:390)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1144)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2863)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1501)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1483)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1457)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:485)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getResultList(EJBQueryImpl.java:742)
at com.mirth.kana.filters.ExpressionBuilderFilter.getElements(ExpressionBuilderFilter.java:156)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:931)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:607)
... 138 more


Seems to me like there needs to be a way for eclipselink to emit dbms_lob.instr(clob,'string') instead of doing a clob = 'string' in the where clause. That would fix this exception. Any thoughts now?

Thanks.

Report message to a moderator

Re: (no subject) [message #704546 is a reply to message #703564] Thu, 28 July 2011 19:22 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1389
Registered: July 2009
Senior Member
I do not believe Oracle allows using = on clobs. You will need to try something else, such as converting the clob in the database to a string, using EclipseLink's JPQL FUNC to use the database TO_CHAR function.

Something like
"select entity.clobField from Entity entity where (FUNC('TO_CHAR', entity.clobField) = '9BSMsjkuGA') and ... "

Best Regards,
Chris

[Updated on: Thu, 28 July 2011 19:24]

Report message to a moderator

(no subject) [message #704553 is a reply to message #703539] Thu, 28 July 2011 19:22 Go to previous message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1389
Registered: July 2009
Senior Member
I do not believe Oracle allows using = on clobs. You will need to try something else, such as converting the clob in the database to a string, using EclipseLink's JPQL FUNC to use the database TO_CHAR function.

Something like
"select entity.clobField from Entity entity where (FUNC(TO_CHAR, entity.clobField) = '9BSMsjkuGA') and ... "

Or use the LIKE operator instead of equals:
"select entity.clobField from Entity entity where (entity.clobField LIKE '9BSMsjkuGA') and ... "


Best Regards,
Chris

[Updated on: Thu, 28 July 2011 19:29]

Report message to a moderator

Previous Topic:(no subject)
Next Topic:Can't find Eclipselink Maven Repository- Error 404
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 03:19:55 GMT 2024

Powered by FUDForum. Page generated in 0.02468 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top

Sign up to our Newsletter

A fresh new issue delivered monthly