Oracle CONTAINS clause?

From: Lawrence Gerstley (lawger..mail.com)
Date: Mon Aug 10 2009 - 20:43:28 EDT

  • Next message: Andrus Adamchik: "Re: Oracle CONTAINS clause?"

    Hello,

    Have you any experience using a query with an Oracle "CONTAINS"
    clause? I'm trying to get results back from a SQLTemplate query from a
    table with CLOBs that have been indexed to improve performance with
    the form of:

    select * from entity where CONTAINS(entityDescription, 'blood%', 1) > 0;

    I can validate that the query is correct by using a tool like SQL
    Squirrel using the same Oracle Thin driver and connection string that
    Cayenne uses, and see a valid result from the query. When the query is
    performed in Cayenne, however, it generates:

    INFO [main] (QueryLogger.java:358) - select * from entity where
    CONTAINS(entityDescription, 'blood%', 1) > 0
      INFO [main] (QueryLogger.java:454) - *** error.
    java.sql.SQLException: Unsupported feature
    at
    oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
    112)
    at
    oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
    146)
    at
    oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
    208)
    at
    oracle
    .jdbc
    .driver
    .DatabaseError.throwUnsupportedFeatureSqlException(DatabaseError.java:
    499)
    at oracle.jdbc.driver.ClobAccessor.getBytes(ClobAccessor.java:279)
    at
    oracle
    .jdbc.driver.OracleResultSetImpl.getBytes(OracleResultSetImpl.java:708)
    at org.apache.cayenne.dba.oracle.OracleSQLTemplateAction
    $OracleResultSetWrapper.getBytes(OracleSQLTemplateAction.java:241)
    at
    org
    .apache
    .cayenne
    .access.types.ByteArrayType.materializeObject(ByteArrayType.java:135)
    at
    org
    .apache
    .cayenne
    .access
    .types
    .ExtendedTypeDecorator.materializeObject(ExtendedTypeDecorator.java:54)
    at
    org
    .apache
    .cayenne
    .access.jdbc.JDBCResultIterator.readDataRow(JDBCResultIterator.java:314)
    at
    org
    .apache
    .cayenne
    .access.jdbc.JDBCResultIterator.nextDataRow(JDBCResultIterator.java:145)
    at
    org
    .apache
    .cayenne
    .access.jdbc.JDBCResultIterator.dataRows(JDBCResultIterator.java:115)
    at
    org
    .apache
    .cayenne
    .access
    .jdbc.SQLTemplateAction.processSelectResult(SQLTemplateAction.java:231)
    at
    org
    .apache
    .cayenne
    .dba
    .oracle
    .OracleSQLTemplateAction
    .processSelectResult(OracleSQLTemplateAction.java:83)
    at
    org
    .apache
    .cayenne.access.jdbc.SQLTemplateAction.execute(SQLTemplateAction.java:
    171)
    at
    org
    .apache
    .cayenne
    .access.jdbc.SQLTemplateAction.performAction(SQLTemplateAction.java:127)
    at
    org
    .apache
    .cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:
    58)
    ...

    Having validated that the query is good, and that the driver is good
    (identical to Squirrel -- ojdbc14.jar), I'm stuck. Is there another
    way to pass-through the query, or something that's limiting Orace 11g
    functions?
    ==============================
    Lawrence Gerstley, Ph.D.
    PSMI Consulting
    lawger..mail.com
    http://www.psmiconsulting.net

    This archive was generated by hypermail 2.0.0 : Mon Aug 10 2009 - 20:44:08 EDT