Order, Order.. Sorting Happens Last

While preparing for my session at Oracle Open World on "Oracle 12c for Developers" I ran into a little remarkable thing.

When sorting a dataset, the sorting is always done last. That is what I was taught anyway. There is probably some obscure way to detect the exact execution plan, but personally I never bothered to go and investigate.
When I was created some test scripts I found there was a way to see that sorting really happens as the final step.

Oracle 12c made some enhancements in the Subquery Factoring clause. Specifically now it is possible to create Functions and Procedures in the WITH clause.
Something like the following:

SQL> with
  2     function formatname (p_name in varchar2)
  3        return varchar2
  4     is
  5     begin
  6        return initcap (p_name);
  7     end formatname;
  8  select ename
  9        ,formatname(ename)  formatted
 10    from emp;

ENAME      FORMATTED
---------- -------------------------
SMITH      Smith
ALLEN      Allen
WARD       Ward
JONES      Jones
MARTIN     Martin
...

After the WITH keyword (first line) a simple function is defined (lines 2 - 7). This function is used in the SELECT part of the query (line 9).

When you want to include a named query in the WITH clause, it has to be after the Procedures and Functions that you have defined.
In the following query, I included a named query called ordered_emps which is a resultset of the EMP table sorted by ENAME.

SQL> set serveroutput on
SQL> with
  2    procedure show (p_what in varchar2)
  3    is
  4    begin
  5      dbms_output.put_line ('input is: '||p_what);
  6    end show;
  7    function formatname (p_name in varchar2)
  8      return varchar2
  9    is
 10    begin
 11       show (p_name);
 12      return initcap (p_name);
 13    end formatname;
 14  ordered_emps as
 15    (select ename from emp order by ename asc)
 16  select ename
 17        ,formatname(ename) formatted
 18    from ordered_emps
 19  /
 
 ENAME       FORMATTED
---------- -----------
ADAMS      Adams
ALLEN      Allen
BLAKE      Blake
CLARK      Clark
FORD       Ford
JAMES      James
JONES      Jones
KING       King
MARTIN     Martin
MILLER     Miller
SCOTT      Scott
SMITH      Smith
TURNER     Turner
WARD       Ward

14 rows selected.

input is: SMITH
input is: ALLEN
input is: WARD
input is: JONES
input is: MARTIN
input is: BLAKE
input is: CLARK
input is: SCOTT
input is: KING
input is: TURNER
input is: ADAMS
input is: JAMES
input is: FORD
input is: MILLER

After the WITH keyword, a procedure (SHOW) is defined which acts as a wrapper for DBMS_OUTPUT.PUT_LINE (lines 2 - 6). Next a function (FORMATNAME) is defined which calls the SHOW procedure with the given input and formats the name to INITCAP (lines 7-13). Next a named query is defined ORDERED_EMPS (lines 14 - 15), and finally the "real" query is specified calling the FORMATNAME function.

Because SERVEROUTPUT is turned on, you can see the results as produced by the query as well as the calls to the SHOW procedure.
The resultset by the query is sorted based on the ENAME as we expect, the calls to the function (and procedure) happen as the data is fetched from the table in no particular order which can be seen in the output from DBMS_OUTPUT.