'Oracle Hierarchical Query ( START WITH, CONNECT BY PRIOR ) using JPA native query: ORA-00933 SQL command not properly ended

Spring boot - Oracle 19 - JPA

I've defined a simple hierarchical table, with a parent/child relationship.

I have the following query:

SELECT
    tnv.*
FROM
    TBL_NOM_VERSION tnv
WHERE
    tnv.INSTITUTION_ID = ?1 START WITH tnv.PARENT_ID IS NULL CONNECT
BY
    PRIOR tnv.ID = tnv.PARENT_ID
ORDER SIBLINGS BY
    tnv.TREE_LEVEL ASC

NOTE: The query works properly in DBeaver, any problem so far!

When I try to execute the query in my spring boot application, I got the following error:

@Query(nativeQuery = true, value = "SELECT * \n" +
        "FROM TBL_NOM_VERSION tnv \n" +
        "WHERE tnv.INSTITUTION_ID = ?1\n" +
        "START WITH tnv.PARENT_ID IS NULL\n" +
        "CONNECT BY PRIOR tnv.ID = tnv.PARENT_ID \n" +
        "ORDER SIBLINGS BY tnv.TREE_LEVEL ASC;")
List<NomVersionView> findAllNomenclatureByInstitution(Long institutionId);

Hibernate: SELECT * FROM TBL_NOM_VERSION tnv WHERE tnv.INSTITUTION_ID = ? START WITH tnv.PARENT_ID IS NULL CONNECT BY PRIOR tnv.ID = tnv.PARENT_ID ORDER SIBLINGS BY tnv.TREE_LEVEL ASC; org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:281) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255) Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

application.yml:

spring:
    datasource:
        driver-class-name: oracle.jdbc.OracleDriver
    jpa:
        database-platform: org.hibernate.dialect.Oracle12cDialect

pom.xml

<dependency>
    <groupId>com.oracle.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>18.3.0.0</version>
</dependency>

What could be the cause? Is it possible to perform sql native hierarchical query using Spring Boot Data? What am I doing wrong?

Thanks!



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source