Problem with a ORACLE LONG datatype field when searching for it with JPA

Question

Solved with a workaround, read in bottom

I have a model called "EmailTransmitido":

@Data
@Entity
@Builder
@Table(schema = "EMAIL", name = "EN_EMAIL_TRANSMITIDO")
public class EmailTransmitido extends EntidadeBase {
...
    @ManyToOne
    @JoinColumn(name = "NRO_EMAIL")
    private EmailMensagem emailMensagem;
...

And another called "EmailMensagem":

@Data
@Entity
@Table(schema = "EMAIL", name = "EN_MENSAGEM_EMAIL")
public class EmailMensagem extends EntidadeBase {

...
    @Column(name = "TXT_TITULO")
    private String txtTitulo;

    @Column(name = "TXT_MENSAGEM")
    private String txtMensagem;
...

Then i need to search through this field "

@Repository
public interface EmailTransmitidoRepository extends JpaRepository {

...
        @Query("SELECT et FROM EmailTransmitido et WHERE et.recursoHumano = ?1 "
                + "AND et.emailMensagem.txtMensagem LIKE %?2%")
        Page getMensagensNotArquivadasByEnRh(
                @Param("enRh") EnRh enRh, @Param("pesquisa") String pesquisa, Pageable pageable);
...

But i receive a error:

Hibernate: 
    select
        * 
    from
        ( select
            emailtrans0_.SEQ_EMAIL_TRANSMITIDO as SEQ_EMAIL_TRANSMIT1_5_,
            emailtrans0_.TXT_CAMPOS_MSG as TXT_CAMPOS_MSG2_5_,
            emailtrans0_.DTA_AGENDA as DTA_AGENDA3_5_,
            emailtrans0_.DTA_ULT_ATUALIZ as DTA_ULT_ATUALIZ4_5_,
            emailtrans0_.DTA_ENVIO as DTA_ENVIO5_5_,
            emailtrans0_.NRO_EMAIL as NRO_EMAIL6_5_,
            emailtrans0_.STA_EMAIL_TESTE as STA_EMAIL_TESTE7_5_,
            emailtrans0_.TXT_USERNAME_ULT_ATUALIZ as TXT_USERNAME_ULT_A8_5_,
            emailtrans0_.NRO_MODELO_DOC as NRO_MODELO_DOC9_5_,
            emailtrans0_.COD_RH as COD_RH17_5_,
            emailtrans0_.STA_EMAIL_TRANSMITIDO as STA_EMAIL_TRANSMI10_5_,
            emailtrans0_.TXT_CAMPOS_MSG_BKP as TXT_CAMPOS_MSG_BK11_5_,
            emailtrans0_.TXT_EMAIL as TXT_EMAIL12_5_,
            emailtrans0_.TXT_EMAIL_COPIA as TXT_EMAIL_COPIA13_5_,
            emailtrans0_.TXT_EMAIL_COPIA_OCULTA as TXT_EMAIL_COPIA_O14_5_,
            emailtrans0_.TXT_ERRO_TRANSMISSAO as TXT_ERRO_TRANSMIS15_5_,
            emailtrans0_.TXT_REMETENTE as TXT_REMETENTE16_5_ 
        from
            EMAIL.EN_EMAIL_TRANSMITIDO emailtrans0_ cross 
        join
            EMAIL.EN_MENSAGEM_EMAIL emailmensa1_ 
        where
            emailtrans0_.NRO_EMAIL=emailmensa1_.NRO_EMAIL 
            and emailtrans0_.COD_RH=? 
            and (
                TO_CLOB(emailmensa1_.TXT_MENSAGEM) like ?
            ) 
        order by
            emailtrans0_.SEQ_EMAIL_TRANSMITIDO desc ) 
    where
        rownum <= ?
2019-06-03 16:49:12.542  WARN 10560 --- [nio-9500-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 932, SQLState: 42000
2019-06-03 16:49:12.542 ERROR 10560 --- [nio-9500-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : ORA-00932: tipos de dados inconsistentes: esperava CHAR obteve LONG

2019-06-03 16:49:12.549 ERROR 10560 --- [nio-9500-exec-2] o.a.c.c.C.[.[.[.[dispatcherServlet]      : Servlet.service() for servlet [dispatcherServlet] in context with path [/cm-api] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause

java.sql.SQLSyntaxErrorException: ORA-00932: tipos de dados inconsistentes: esperava CHAR obteve LONG

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447) ~[ojdbc6.jar:11.2.0.4.0]
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) ~[ojdbc6.jar:11.2.0.4.0]
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951) ~[ojdbc6.jar:11.2.0.4.0]
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513) ~[ojdbc6.jar:11.2.0.4.0]
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227) ~[ojdbc6.jar:11.2.0.4.0]
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) ~[ojdbc6.jar:11.2.0.4.0]
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208) ~[ojdbc6.jar:11.2.0.4.0]
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886) ~[ojdbc6.jar:11.2.0.4.0]
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175) ~[ojdbc6.jar:11.2.0.4.0]
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296) ~[ojdbc6.jar:11.2.0.4.0]
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613) ~[ojdbc6.jar:11.2.0.4.0]
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657) ~[ojdbc6.jar:11.2.0.4.0]
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495) ~[ojdbc6.jar:11.2.0.4.0]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-3.2.0.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-3.2.0.jar:na]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2167) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1930) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1892) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:937) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:340) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2689) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2672) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2506) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.loader.Loader.list(Loader.java:2501) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:504) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:395) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:220) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1508) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1537) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1505) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.query.Query.getResultList(Query.java:135) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:194) ~[spring-data-jpa-2.1.5.RELEASE.jar:2.1.5.RELEASE]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:91) ~[spring-data-jpa-2.1.5.RELEASE.jar:2.1.5.RELEASE]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:136) ~[spring-data-jpa-2.1.5.RELEASE.jar:2.1.5.RELEASE]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:125) ~[spring-data-jpa-2.1.5.RELEASE.jar:2.1.5.RELEASE]

This kind of error "java.sql.SQLSyntaxErrorException: ORA-00932: tipos de dados inconsistentes: esperava CHAR obteve LONG" that means "expected CHAR got LONG" would be something related to the LONG datatype in Oracle be deprecated? i mean, others field that uses differents datatypes works well with similar search queries.

I tried to make use of the @Formula instead of @Column, but it didn't work:

@Formula("TOCLOB(TXT_MENSAGEM)")
private String txtMensagem;

I tried to use JPQ Query method too but the problem persists, only with this specific LONG field. My main problem is that i can't change the datatype in the Oracle because it's a client's old database from where i only can read, and i don't have permission to make changes there, so is there some workaround to compare this field with JPA?

I solved this problem with another table created to keep those data in CLOB format. As the column is of a fixed value and the consumer didn't object to it. Need to say here that i searched a lot and appears to don't have a good workaround for it yet, so if you face this problem too, think about doing the same or change definitively you column data type. Thanks all for the help.

Simon Martinelli · Accepted Answer

You should be able to map Long as LOB like:

@Lob
@Column(name = "TXT_MENSAGEM")
private String txtMensagem;

Problem with a ORACLE LONG datatype field when searching for it with JPA

Answers (1)

Related Questions