0

Currently using Oracle for production and H2 for local. Oracle throws an error when a WITH ANCESTORS is used but H2 continues on indefinitely. I want to make an agnostic query that fails in both, or at the very least can check if a loop occurs.

WITH ANCESTORS (ID, SUPERVISOR_ID) AS
    (SELECT ID, SUPERVISOR_ID
     FROM EMPLOYEE
     WHERE ID = :id

     UNION ALL
     
     SELECT E2.ID, E2.SUPERVISOR_ID
     FROM ANCESTORS E1
         JOIN EMPLOYEE E2 ON E1.SUPERVISOR_ID = E2.ID)
SELECT ID, SUPERVISOR_ID
FROM ANCESTORS;


Employee

ID  | SUPERVISOR_ID
1   |  2
2   |  3
3   |  4
4   |  1

This throws a nice error in oracle, thus works properly in prod, but not so much in H2 for development.

Sir Nixy
  • 136
  • 1
  • 5
  • 2
    Oracle and H2 are two different databases. Even if H2 tries to simulate the Oracle syntax to a point, there are so many differences between them that you won't be able to simulate every last detail of Oracle in H2. If that's your goal, then you would be better off spinning up an Oracle 21 docker image on-demand locally in your dev environment, as needed. – The Impaler Aug 18 '22 at 15:43
  • Does this answer your question? [Infinite loop in H2 query](https://stackoverflow.com/questions/37141837/infinite-loop-in-h2-query) – flyaround Sep 05 '22 at 14:55

0 Answers0