Hello everyone, we are facing a very strange situation. We had two Oracle 11g db, with dblinks pointing to each other. Now, one of these db has been upgraded to 19, still keeping the dblinks. We have problems with some queries that are generated dynamically by an application. The problem is when the query contains a sintax like this:
SELECT LISTAGG (dummy, :p1) WITHIN GROUP (ORDER BY NULL)
FROM DUAL@my_dblink
WHERE
:p2 BETWEEN dummy AND dummy;
when executing this query in the db version 19 and my_dblink points to a version 11 db (with any value of P1 and P2) we get this error:
ORA-02000: missing WITHIN keyword
Some details:
- no error executing the same query from version 11 pointing to 19, or from 11 pointing to 11
- no error if you remove the @my_dblink
- no error replacing ORDER BY NULL with ORDER BY DUMMY or ORDER BY dbms_random.value; same error using ORDER BY any constant value
- no error replacing :P1 and :P2 with constant values
- no error using ≤ and ≥ instead of between
- no error with a condition WHERE DUMMY BETWEEN :p2 AND :p3
There are many workarounds but, as I said, the queries are generated by the application, so it could be an hard work to find and modify the queries in all situations.
Thanks for any suggestions