'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 |
|---|
