Hibernate + Spring with Oracle - Use hierarchical query (START WITH + CONNECT BY)

Question

I am having a problem when I try to execute a native sql query which uses hierarchical clauses START WITH and CONNECT BY in my JUnit Test.

I explain the steps I have followed:

1. My native SQL query looks like this:

   SELECT EE.* FROM SEDU_EE_ESTRUCT EE 
   START WITH EE.ID_EE_ESTRUCT = 2 
   CONNECT BY PRIOR EE.ID_EE_ESTRUCT = EE.ID_EE_ESTRUCT_PARENT;
   

I have tested it against my DB (Oracle 10g) using Sql Developer and everything goes fine. It does work.

My problem comes when I try to execute it in a Java method (using Spring, Hibernate and native queries). In this case, the steps are:

1. Declare an String with my query:

   private static final String SQL_RECURSIVE_NODES = "SELECT EE.* "
         + "FROM SEDU_EE_ESTRUCT EE "
         + "START WITH EE.ID_EE_ESTRUC = :idNode "
         + "CONNECT BY PRIOR EE.ID_EE_ESTRUCT = EE.ID_EE_ESTRUCT_PARENT";
   

2. Make my SQLQuery object with that String:

   SQLQuery myQuery = getSessionFactory().getCurrentSession().createSQLQuery(SQL_RECURSIVE_NODES);
   

3. Just execute it:

   List results = myQuery.list();
   

But in this case it doesn´t work... I get the Hibernate SQLGrammarException and in the trace it can be read:

org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: java.sql.SQLException: Unexpected token: START in statement

What could be the reason for this error?

Answer 1

I have solved it. The problem was I was executing the query inside a JUnit test, and we are not using Oracle DB for testing, but Hypersonic (ooucchh!!). So the problem is that START WITH and CONNECT BY are clauses of Oracle databases only.

Thanks all.

Regards