CODEWITHSUNDEEP
javaoracle-databasehibernatejpa
Alex
Alex

Reputation: 160

Hibernate generates invalid sql with oracle

I'm using hibernate 4.2.1 with Oracle11g. During the translation of a NamedQuery apparently hibernate generates an invalid SQL statement. The NamedQuery looks like this:

SELECT retenues FROM EnsembleRetenuesPrestation retenues
WHERE EXISTS (SELECT calcul FROM CalculRetenueGlobale calcul
              WHERE calcul.id.payementPrestation.id = :payementId
              AND calcul.id.ensembleRetenuesPrestation.id = retenues.id)

CalculRetenueGlobale is a join table (with some additional fields. So it's a real entity.) for EnsembleRetenuesPrestation and PayementPrestation. It uses an embedded key as primary key containing the two entities CalculRetenueGlobale and PayementPrestation.

The generated SQL is the following:

select
    ensemblere0_.ERP_ID_ENSEMBLE_RET_PREST as ERP1_24_,
    ensemblere0_.ERP_AAAAMM_ARRERAGES as ERP2_24_,
    ensemblere0_.ERP_CODE_CALCUL_SIMUL as ERP3_24_,
    ensemblere0_.ERP_ID_EXCEDENT_RETENUE as ERP19_24_,
    ensemblere0_.ERP_FLAG_5E_TRANCHE_CESSION as ERP4_24_,
    ensemblere0_.ERP_FLAG_ARRERAGES as ERP5_24_,
    ensemblere0_.ERP_MONTANT_APRES_RETENUES as ERP6_24_,
    ensemblere0_.ERP_MONTANT_AVANT_RETENUES as ERP7_24_,
    ensemblere0_.ERP_MONTANT_CESSIBLE as ERP8_24_,
    ensemblere0_.ERP_MONTANT_INCESS_INSAISISS as ERP9_24_,
    ensemblere0_.ERP_MONTANT_SAISISSABLE as ERP10_24_,
    ensemblere0_.ERP_RETENUE_ACOMPTE as ERP11_24_,
    ensemblere0_.ERP_RETENUE_ACOMPTE_FIXE as ERP12_24_,
    ensemblere0_.ERP_RETENUE_CONJOINT_TIERS as ERP13_24_,
    ensemblere0_.ERP_RETENUE_FAILLITE as ERP14_24_,
    ensemblere0_.ERP_RETENUE_SURENDETTEMENT as ERP15_24_,
    ensemblere0_.ERP_SOM_RET_ALIM as ERP16_24_,
    ensemblere0_.ERP_SOM_RET_CESSION as ERP17_24_,
    ensemblere0_.ERP_SOM_RET_SAISIES as ERP18_24_ 
from
    CCRSC.ENSEMBLE_RET_PREST ensemblere0_ 
where
    exists (
        select
            (calculrete1_.CAL_ID_CREANCE,
            calculrete1_.CAL_ID_ENSEMBLE_RET_PREST,
            calculrete1_.CAL_ID_PAIEMENT)
        from
            CCRSC.CALC_RET_GLOBALE calculrete1_ 
        where
            calculrete1_.CAL_ID_PAIEMENT=1
            and calculrete1_.CAL_ID_ENSEMBLE_RET_PREST=ensemblere0_.ERP_ID_ENSEMBLE_RET_PREST
    )

The result is ORA-00907: missing right parenthesis. If I remove the two parentheses in this part I can execute the query without problems (in SQLDeveloper for example):

select
    (calculrete1_.CAL_ID_CREANCE,
     calculrete1_.CAL_ID_ENSEMBLE_RET_PREST,
     calculrete1_.CAL_ID_PAIEMENT)

I could only find an old problem (https://hibernate.atlassian.net/browse/HHH-2409), but in this case they blame the 'AS' keyword which doesn't seem to be the problem in this case.

Has anyone already encountered similar problems? Could this be a mapping issue?

Thanks in advance,

Alex

Upvotes: 2

Views: 1897

Answers (1)

Brian
Brian

Reputation: 7289

Putting a field list in brackets will produce the error on even trivial queries so it is not Oracle parsing the query incorrectly.

select (1,2,3) FROM DUAL

A workaround suggested for an older version of Hibernate is to return a literal value instead of a field in the EXISTS(). (https://hibernate.atlassian.net/browse/HHH-2845)

SELECT retenues FROM EnsembleRetenuesPrestation retenues
WHERE EXISTS (SELECT 1 FROM CalculRetenueGlobale calcul
              WHERE calcul.id.payementPrestation.id = :payementId
              AND calcul.id.ensembleRetenuesPrestation.id = retenues.id)

The issue is also noted in (https://hibernate.atlassian.net/browse/HHH-5998)

Upvotes: 4

Related Questions