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] Tue, 26 July 2011 20:39 Go to next message
Brendan Haverlock is currently offline 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
Re: Using CLOBs in entities with Oracle [message #703484 is a reply to message #702933] Wed, 27 July 2011 12:08 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 995
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
Re: Using CLOBs in entities with Oracle [message #703539 is a reply to message #703484] Wed, 27 July 2011 13:12 Go to previous messageGo to next message
Brendan Haverlock is currently offline 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 Go to previous messageGo to next message
Brendan Haverlock is currently offline 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;
Re: (no subject) [message #704352 is a reply to message #703539] Thu, 28 July 2011 10:23 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Can you include the full exception stack trace.

James : Wiki : Book : Blog : Twitter
(no subject) [message #704366 is a reply to message #703539] Thu, 28 July 2011 10:23 Go to previous messageGo to next message
James is currently offline 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/
Re: (no subject) [message #704472 is a reply to message #704352] Thu, 28 July 2011 13:11 Go to previous messageGo to next message
Brendan Haverlock is currently offline 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.
(no subject) [message #704490 is a reply to message #704352] Thu, 28 July 2011 13:11 Go to previous messageGo to next message
Brendan Haverlock is currently offline 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.
Re: (no subject) [message #704546 is a reply to message #703564] Thu, 28 July 2011 15:22 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 995
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 Go to previous message
Chris Delahunt is currently offline Chris Delahunt
Messages: 995
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:
  


Current Time: Sat Apr 19 00:23:27 EDT 2014

Powered by FUDForum. Page generated in 0.01876 seconds