FHIR Chat · JPA SearchBuilder Issue - Oracle · hapi

Stream: hapi

Topic: JPA SearchBuilder Issue - Oracle

Joe Atterberry (Feb 04 2021 at 18:38):

This is also in #implementers.

HAPI Version 5.2 - Oracle 18xe - ojdbc 19.3.0.0

I'm using the hapi-fhir-jpaserver-starter with the new JPA SearchBuilder enabled (the default).
On startup, one of the initial SQL statements that is issued returns: 'ORA-00907: missing right parenthesis' multiple times and the app never starts.
I enabled showing SQL and found this SQL statement generated:

SELECT

    * 
from
    ( SELECT
        RES_ID 
    FROM
        ( SELECT
            t0.RES_ID 
        FROM
            HFJ_RESOURCE t0 
        WHERE
            (
                (
                    t0.RES_TYPE = ?
                ) 
                AND (
                    t0.RES_DELETED_AT IS NULL
                )
            )
         )
         AS RES_ID 
     )
    WHERE
        rownum <= ?

The issue is the 'AS RES_ID'. As far as Oracle is concerned, aliases on sub queries generally are not allowed. If that line is removed, the query works fine. I set the SQL Builder back to legacy mode and don't get this error. I did nothing to the configuration except set it up to use Oracle.

Let me know if more information is needed.

Thanks.

Last updated: Apr 12 2022 at 19:14 UTC