I created some reports that make use of the code WITH FUNCTION (…) for efficiency. The trouble is that, when I sort the result (by double clicking in the report column), I systematically get the error "ORA-32034: unsupported use of WITH clause".
This is due to the fact that ordering the column generates a new query doing a SELECT * FROM (<query_including_with_function>) ORDER BY x. This will only work if we add the hint /*+ WITH_PLSQL */ in that generated query.
Could we add a functionality that either systematically add the hint to the sorting query, or checks the original query for the “WITH FUNCTION” clause, and then add the hint?
You can easily reproduce the error by using the following query in a SQL Worksheet and then sorting the output:
WITH
FUNCTION dummy_fct RETURN VARCHAR2 IS
BEGIN
RETURN 'a';
END dummy_fct;
SELECT dummy_fct()
FROM dual;
/
The following would work:
SELECT /*+ WITH_PLSQL */ *
FROM (
WITH
FUNCTION dummy_fct RETURN VARCHAR2 IS
BEGIN
RETURN 'a';
END dummy_fct;
SELECT dummy_fct()
FROM dual
)
ORDER BY 1;
/
(remove the hint, and the query will not be understood by SQL Developer)