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

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

Using CLOBs in entities with Oracle [message #702933]

Tue, 26 July 2011 20:39

Eclipse User

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

Re: Using CLOBs in entities with Oracle [message #703484 is a reply to message #702933]

Wed, 27 July 2011 12:08

Eclipse User

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

Re: Using CLOBs in entities with Oracle [message #703539 is a reply to message #703484]

Wed, 27 July 2011 13:12

Eclipse User

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] by Moderator

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

Wed, 27 July 2011 13:12

Eclipse User

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;

Re: (no subject) [message #704352 is a reply to message #703539]

Thu, 28 July 2011 10:23

Eclipse User

Can you include the full exception stack trace.

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

Thu, 28 July 2011 10:23

Eclipse User

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/

Re: (no subject) [message #704472 is a reply to message #704352]

Thu, 28 July 2011 13:11

Eclipse User

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.

(no subject) [message #704490 is a reply to message #704352]

Thu, 28 July 2011 13:11

Eclipse User

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.

Re: (no subject) [message #704546 is a reply to message #703564]

Thu, 28 July 2011 15:22

Eclipse User

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] by Moderator

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

Thu, 28 July 2011 15:22

Eclipse User

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] by Moderator

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

Goto Forum:

-=] Back to Top [=-

Current Time: Sun Jul 06 05:14:13 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter