I am using OJB 1.0.4, Persistence Broker, Oracle 10g, Oracle Text.
I have a query which is using the Oracle Text contains() function to
search in a CLOB. It is throwing ORA-00932: inconsistent datatypes:
expected - got CLOB.
If I replace the ?'s with appropriate values and do
Statement.executeQuery(), i.e. do the binding by hand and pass this query
straight to JDBC, then it works. Similarly, if I give it to sqlplus, it
works. Can anybody tell me why it fails when produced by OJB? The one
possibly weird thing that I can come up with is that 'SPECIES', the value
that's being sought in the CLOB column, is not a bind variable in the
PreparedStatement but rather a literal (I couldn't figure out how to do it
otherwise with PB).
Any insights?
thanks,
-steve
Steve Clark
ECOS Development Group
[EMAIL PROTECTED]
(970)226-9291
Full stack trace:
An error has occurred
org.apache.ojb.broker.PersistenceBrokerSQLException: * SQLException during
execution of sql-statement: * sql statement was 'SELECT DISTINCT
A0.ACTIVITY_ID,A0.PRIMARY_WORK_TYPE_ID,A0.IS_EARLY_PLANNING,A0.IS_MITIGATION_REQUIRED,A0.ACRES_IMPACTED,A0.MILES_IMPACTED,A0.S18_FISHWAY_PRESCRIPTIONS,A0.S18_FISHWAY_MILES,A0.OTHER_RIVER_MILES,A0.FWS_RESPONSE_DATE,A0.FINAL_DECISION_DATE,A0.PERMIT_DATE,A0.PERMIT_AGENCY_ID,A0.OTHER_PERMIT_AGENCY,A0.REF_ENVIR_REVIEW,A0.REF_FOIA_CODE,A0.FEDACT_REVIEW_TYPE_ID,A0.APPLICANT_TYPE_ID,A0.PERMIT_STATUS_ID,A0.FWS_RECOMMENDATION,A1.SEQUENCE
as ojb_col_21 FROM TAILS_DEV.FEDERAL_ACTIVITY A0 INNER JOIN
TAILS_DEV.ACTIVITY A1 ON A0.ACTIVITY_ID=A1.ACTIVITY_ID WHERE
A0.ACTIVITY_ID IN (SELECT DISTINCT B0.ACTIVITY_ID FROM
TAILS_DEV.FEDERAL_ACTIVITY B0 LEFT OUTER JOIN TAILS_DEV.ACTIVITY B1 ON
B0.ACTIVITY_ID=B1.ACTIVITY_ID LEFT OUTER JOIN TAILS_DEV.ACTIVITY_EVENT B2
ON B1.ACTIVITY_ID=B2.ACTIVITY_ID LEFT OUTER JOIN TAILS_DEV.EVENT B3 ON
B2.EVENT_ID=B3.EVENT_ID LEFT OUTER JOIN TAILS_DEV.ACTIVITY B4 ON
B0.ACTIVITY_ID=B4.ACTIVITY_ID LEFT OUTER JOIN
TAILS_DEV.ACTIVITY_OTHER_OFFICE B5 ON B4.ACTIVITY_ID=B5.ACTIVITY_ID LEFT
OUTER JOIN ECOS_DATA.ECOS_OFFICE B6 ON B5.OFFICE_ID=B6.OFFICE_ID WHERE (
contains(B3.DESCRIPTION,'SPECIES') > ?) AND (B4.LEAD_OFFICE_ID = ? OR
(B6.OFFICE_ID = ?))) ORDER BY 21' * Exception message is [ORA-00932:
inconsistent datatypes: expected - got CLOB ] * Vendor error code [932] *
SQL state code [42000]
org.apache.ojb.broker.PersistenceBrokerException:
org.apache.ojb.broker.PersistenceBrokerSQLException:
* SQLException during execution of sql-statement:
* sql statement was 'SELECT DISTINCT
A0.ACTIVITY_ID,A0.PRIMARY_WORK_TYPE_ID,A0.IS_EARLY_PLANNING,A0.IS_MITIGATION_REQUIRED,A0.ACRES_IMPACTED,A0.MILES_IMPACTED,A0.S18_FISHWAY_PRESCRIPTIONS,A0.S18_FISHWAY_MILES,A0.OTHER_RIVER_MILES,A0.FWS_RESPONSE_DATE,A0.FINAL_DECISION_DATE,A0.PERMIT_DATE,A0.PERMIT_AGENCY_ID,A0.OTHER_PERMIT_AGENCY,A0.REF_ENVIR_REVIEW,A0.REF_FOIA_CODE,A0.FEDACT_REVIEW_TYPE_ID,A0.APPLICANT_TYPE_ID,A0.PERMIT_STATUS_ID,A0.FWS_RECOMMENDATION,A1.SEQUENCE
as ojb_col_21 FROM TAILS_DEV.FEDERAL_ACTIVITY A0 INNER JOIN
TAILS_DEV.ACTIVITY A1 ON A0.ACTIVITY_ID=A1.ACTIVITY_ID WHERE
A0.ACTIVITY_ID IN (SELECT DISTINCT B0.ACTIVITY_ID FROM
TAILS_DEV.FEDERAL_ACTIVITY B0 LEFT OUTER JOIN TAILS_DEV.ACTIVITY B1 ON
B0.ACTIVITY_ID=B1.ACTIVITY_ID LEFT OUTER JOIN TAILS_DEV.ACTIVITY_EVENT B2
ON B1.ACTIVITY_ID=B2.ACTIVITY_ID LEFT OUTER JOIN TAILS_DEV.EVENT B3 ON
B2.EVENT_ID=B3.EVENT_ID LEFT OUTER JOIN TAILS_DEV.ACTIVITY B4 ON
B0.ACTIVITY_ID=B4.ACTIVITY_ID LEFT OUTER JOIN
TAILS_DEV.ACTIVITY_OTHER_OFFICE B5 ON B4.ACTIVITY_ID=B5.ACTIVITY_ID LEFT
OUTER JOIN ECOS_DATA.ECOS_OFFICE B6 ON B5.OFFICE_ID=B6.OFFICE_ID WHERE (
my_contains(B3.DESCRIPTION,'E') > ?) AND (B4.LEAD_OFFICE_ID = ? OR
(B6.OFFICE_ID = ?))) ORDER BY 21'
* Exception message is [ORA-00932: inconsistent datatypes: expected - got
CLOB
]
* Vendor error code [932]
* SQL state code [42000]
at
org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:272)
at
org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:284)
at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getCollectionByQuery(PersistenceBrokerImpl.java:1475)
at
org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(DelegatingPersistenceBroker.java:383)
at
org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(DelegatingPersistenceBroker.java:383)
at gov.doi.tat.odmg.Select.byCriteria(Select.java:314)
at gov.doi.tat.odmg.Select.byCriteria(Select.java:265)
at gov.doi.tat.odmg.Select.byCriteriaWithSubQuery(Select.java:242)
at
gov.doi.tails.dao.odmg.AbstractSearchDaoImpl.search(AbstractSearchDaoImpl.java:306)
at
gov.doi.tails.biz.AbstractSearchMgr.search(AbstractSearchMgr.java:129)
at
gov.doi.tails.actions.AbstractSearchAction.doSearch(AbstractSearchAction.java:224)
at
gov.doi.tails.actions.SearchFedActsAction.getResults(SearchFedActsAction.java:31)
at
gov.doi.tails.actions.AbstractSearchAction.doExec(AbstractSearchAction.java:55)
at gov.doi.tails.actions.TailsAction.execute(TailsAction.java:104)
at
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
at
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
at
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
at
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
at
gov.doi.tat.servlet.TatActionServlet.doPost(TatActionServlet.java:148)
at
gov.doi.tails.util.TailsController.doPost(TailsController.java:159)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at
org.displaytag.filter.ResponseOverrideFilter.doFilter(ResponseOverrideFilter.java:125)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:213)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:551)
at
gov.doi.ecos.catalina.valves.EcosAuthenticator.invoke(EcosAuthenticator.java:163)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at
gov.doi.ecos.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:581)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at
org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2422)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at
org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:163)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:577)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at
org.apache.catalina.authenticator.EcosSingleSignOn.invoke(EcosSingleSignOn.java:193)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at
org.apache.catalina.valves.RequestDumperValve.invoke(RequestDumperValve.java:221)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at
org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:199)
at
org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:324)
at
org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:395)
at
org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:673)
at
org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:615)
at
org.apache.jk.common.SocketConnection.runIt(ChannelSocket.java:786)
at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:534)
Caused by: org.apache.ojb.broker.PersistenceBrokerSQLException:
* SQLException during execution of sql-statement:
* sql statement was 'SELECT DISTINCT
A0.ACTIVITY_ID,A0.PRIMARY_WORK_TYPE_ID,A0.IS_EARLY_PLANNING,A0.IS_MITIGATION_REQUIRED,A0.ACRES_IMPACTED,A0.MILES_IMPACTED,A0.S18_FISHWAY_PRESCRIPTIONS,A0.S18_FISHWAY_MILES,A0.OTHER_RIVER_MILES,A0.FWS_RESPONSE_DATE,A0.FINAL_DECISION_DATE,A0.PERMIT_DATE,A0.PERMIT_AGENCY_ID,A0.OTHER_PERMIT_AGENCY,A0.REF_ENVIR_REVIEW,A0.REF_FOIA_CODE,A0.FEDACT_REVIEW_TYPE_ID,A0.APPLICANT_TYPE_ID,A0.PERMIT_STATUS_ID,A0.FWS_RECOMMENDATION,A1.SEQUENCE
as ojb_col_21 FROM TAILS_DEV.FEDERAL_ACTIVITY A0 INNER JOIN
TAILS_DEV.ACTIVITY A1 ON A0.ACTIVITY_ID=A1.ACTIVITY_ID WHERE
A0.ACTIVITY_ID IN (SELECT DISTINCT B0.ACTIVITY_ID FROM
TAILS_DEV.FEDERAL_ACTIVITY B0 LEFT OUTER JOIN TAILS_DEV.ACTIVITY B1 ON
B0.ACTIVITY_ID=B1.ACTIVITY_ID LEFT OUTER JOIN TAILS_DEV.ACTIVITY_EVENT B2
ON B1.ACTIVITY_ID=B2.ACTIVITY_ID LEFT OUTER JOIN TAILS_DEV.EVENT B3 ON
B2.EVENT_ID=B3.EVENT_ID LEFT OUTER JOIN TAILS_DEV.ACTIVITY B4 ON
B0.ACTIVITY_ID=B4.ACTIVITY_ID LEFT OUTER JOIN
TAILS_DEV.ACTIVITY_OTHER_OFFICE B5 ON B4.ACTIVITY_ID=B5.ACTIVITY_ID LEFT
OUTER JOIN ECOS_DATA.ECOS_OFFICE B6 ON B5.OFFICE_ID=B6.OFFICE_ID WHERE (
my_contains(B3.DESCRIPTION,'E') > ?) AND (B4.LEAD_OFFICE_ID = ? OR
(B6.OFFICE_ID = ?))) ORDER BY 21'
* Exception message is [ORA-00932: inconsistent datatypes: expected - got
CLOB
]
* Vendor error code [932]
* SQL state code [42000]
at
org.apache.ojb.broker.util.ExceptionHelper.generateException(ExceptionHelper.java:256)
at
org.apache.ojb.broker.util.ExceptionHelper.generateException(ExceptionHelper.java:90)
at
org.apache.ojb.broker.util.ExceptionHelper.generateException(ExceptionHelper.java:71)
at
org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:327)
at
org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(RsQueryObject.java:72)
at
org.apache.ojb.broker.accesslayer.RsIterator.(RsIterator.java:187)
at
org.apache.ojb.broker.core.RsIteratorFactoryImpl.createRsIterator(RsIteratorFactoryImpl.java:58)
at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(PersistenceBrokerImpl.java:2061)
at
org.apache.ojb.broker.core.PersistenceBrokerImpl.getIteratorFromQuery(PersistenceBrokerImpl.java:1619)
at
org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:120)
at
org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:264)
... 66 more
Caused by: java.sql.SQLException: ORA-00932: inconsistent datatypes:
expected - got CLOB
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:158)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:626)
at
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:182)
at
oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:774)
at
oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:977)
at
oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:817)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1094)
at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3015)
at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3056)
at
org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:294)
... 73 more
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
