Re: ORA-00932 inconsistent datatypes with CLOB
Hi Steve, [EMAIL PROTECTED] wrote: ... As an aside, it sure would be nice to be able to add new Platform implementations without having to put them into org.apache.ojb.broker.platforms. A simple solution would be something like this (in PlatformFactory): I agree with you. A more sophisticated implementation of PlatformFactory#getPlatformFor is required - add this on my todo-list. regards, Armin - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: ORA-00932 inconsistent datatypes with CLOB
Hello Steve, What does your criteria code look like? I have the same problem when applying like operation on a CLOB. I therefore changed the fieldtype to varchar2(4000). Cheers, Dennis
Re: ORA-00932 inconsistent datatypes with CLOB
Hi Steve, do you use a modified version of JdbcAccessImpl? The stack trace show line 327 in method 'executeQuery' as causer but this doesn't match with 1.0.4 version of this class (SNV: 365259) ... at org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:327) regards, Armin [EMAIL PROTECTED] wrote: 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
Re: ORA-00932 inconsistent datatypes with CLOB
I guess I had modified JdbcAccessImpl to add some print statements.
I have solved the problem. Here's what was happening:
I had code like this: crit.addGreaterThan(contains(clob_column,
'someval'), 0), because I needed to get SQL like this:
contains(clob_column, 'someval') 0. When OJB parsed the first parameter
to extract the referenced column, it decided (correctly) that it was
looking at a CLOB. So when it went to bind the second parameter (0), it
tried to handle it as a CLOB as well, which (of course) didn't work.
The workaround that I am using: I made a custom platform implementation
which overrides setObjectForStatement() to look for the special case where
a String containing a single digit is being treated as a CLOB and treat it
as an integer instead. This test, at least in my app, will pretty
reliably indicate this specific situation.
I'm not sure whether there's a cleaner solution short of making OJB aware
of functions and their specific return types.
As an aside, it sure would be nice to be able to add new Platform
implementations without having to put them into
org.apache.ojb.broker.platforms. A simple solution would be something
like this (in PlatformFactory):
private static String getClassnameFor(String platform)
{
String pf = Default;
if (platform != null)
{
+if (platform.indexOf('.') 0)
+{
+return platform;
+}
pf = platform;
}
return org.apache.ojb.broker.platforms.Platform +
pf.substring(0, 1).toUpperCase() + pf.substring(1) + Impl;
}
-steve
Steve Clark
ECOS Development Group
[EMAIL PROTECTED]
(970)226-9291
Armin Waibel [EMAIL PROTECTED]
07/28/2006 11:35 AM
Please respond to
OJB Users List ojb-user@db.apache.org
To
OJB Users List ojb-user@db.apache.org
cc
Subject
Re: ORA-00932 inconsistent datatypes with CLOB
Hi Steve,
do you use a modified version of JdbcAccessImpl? The stack trace show
line 327 in method 'executeQuery' as causer but this doesn't match with
1.0.4 version of this class (SNV: 365259)
...
at
org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:327)
regards,
Armin
[EMAIL PROTECTED] wrote:
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
ORA-00932 inconsistent datatypes with CLOB
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
