SQL Developer

[EDITED on 25 April 2023, in order to restore as much as possible of text formats lost in the migration to the new Forums software.]

Dear ~Santa~ SQL Developer team,

Here's an idea—possibly not new, but hopefully cool—for ~Christmas~ 21.4: make it possible to sort or filter data in the table data grid when the initial query contains inline PL/SQL functions defined in the WITH clause.

Since DB version 12.1 it's been possible to declare and define PL/SQL functions in the WITH clause, e.g.:

with function f(p_n in number) return number
is begin
    return case
            when p_n <= 0 then 1
            else f(floor(p_n) - 1) + f(floor(p_n) - 2)
           end;
end f;
select
    level - 1     as "n",
    f(level - 1)  as "f(n)"
from
    dual
connect by
    level <= 10;
/

Unfortunately, that SQL feature is not fully supported by the data grid in SQL Developer, in that it does not permit to filter data (by clicking on the column header) nor sort it (by clicking on the up/down arrow): attempting to do either one results in the following exception:

ORA-32034: unsupported use of WITH clause
32034. 00000 -  "unsupported use of WITH clause"
*Cause:    Inproper use of WITH clause because one of the following two reasons
           1. nesting of WITH clause within WITH clause not supported yet
           2. For a set query, WITH clause can't be specified for a branch.
           3. WITH clause cannot be specified within parenthesis.
*Action:   correct query and retry

Reason: in order to sort or filter, the data grid in SQL Developer wraps the original query into a new one, with the sorting or filtering criteria added, e.g.:

select  * from (    -----> Wrapping query added by SQL Developer
with function f(p_n in number) return number
is begin
    return case
            when p_n <= 0 then 1
            else f(floor(p_n) - 1) + f(floor(p_n) - 2)
           end;
end f;
select
    level - 1     as "n",
    f(level - 1)  as "f(n)"
from
    dual
connect by
    level <= 10
 )  WHERE "n"  = 4    -----> Sample filtering criteria, added by SQL Developer

And that simple transformation does not take into account the presence of a PL/SQL function inside the WITH clause of the original query. It turns out, the resulting query is not syntactically valid. Per the SQL Reference manual, the plsql_declarations sub-clause of the with_clause clause must be put in the top-level query, or if it is not, the top-level query must contain the WITH_PLSQL hint. That's true: all that separates the above query from being syntactically valid is the missing /*+ WITH_PLSQL */ hint after the main select keyword.

Possible solution: make the process of building the wrapping query a bit smarter by enabling it to detect the presence of PL/SQL declarations nested in WITH clauses—one must parse the query and analyze the parse tree to do that cleanly, but I gather that it's a simple query in Arbori—then add the WITH_PLSQL hint to the top-level query if necessary.

One may argue that PL/SQL functions defined in the WITH clause is ostentatious luxury... It most certainly is, but now that we have it:

• There are times when ~I'm lazy~ I can't ~bother~ afford to create a stored function or a package
• The current state of support of that feature in SQL Developer's data grid almost precludes using it in reports: if I ship a report to end users, they will expect to be able to filter or sort data in the data grid as they see fit, and if that breaks they will just think that my report is broken, not the data grid.

So, all things considered, I think this could be a small, but valuable enhancement.

Regards,