Eclipse Community Forums: EclipseLink » Using CLOBs in entities with Oracle

Help

Home

Home » Eclipse Projects » EclipseLink » Using CLOBs in entities with Oracle

Show: Today's Messages :: Show Polls :: Message Navigator

Using CLOBs in entities with Oracle [message #702933]

Tue, 26 July 2011 20:39

Brendan Haverlock
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 12:08

Chris Delahunt
Messages: 862
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 13:12

Brendan Haverlock
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 13:13]

Report message to a moderator

(no subject) [message #703564 is a reply to message #703484]

Wed, 27 July 2011 13:12

Brendan Haverlock
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 10:23

James Sutherland
Messages: 1834
Registered: July 2009

Senior Member

Can you include the full exception stack trace.

James : Wiki : Book : Blog

Report message to a moderator

(no subject) [message #704366 is a reply to message #703539]

Thu, 28 July 2011 10:23

James
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 13:11

Brendan Haverlock
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 13:11

Brendan Haverlock
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 15:22

Chris Delahunt
Messages: 862
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 15:24]

Report message to a moderator

(no subject) [message #704553 is a reply to message #703539]

Thu, 28 July 2011 15:22

Chris Delahunt
Messages: 862
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 15:29]

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 19 07:50:34 EDT 2013