Oracle 12c / User-defined function in the WITH clause

The option to define a PL/SQL function or procedure inside an SQL query might seem strange at first glance, but it has one significant advantage – the database does not need to switch context, which in certain cases leads to a massive increase in performance.

WITH
  FUNCTION mf_Upper(
        p_Input VARCHAR2
    ) RETURN VARCHAR2
  IS
  BEGIN
    RETURN UPPER(p_Input);
  END;
SELECT mf_Upper('test') AS upper_dummy
  FROM dual
/

The same (if not better) result, while maintaining clarity, can also be achieved by using the PRAGMA UDF directive when declaring the method at the object or package level.

CREATE OR REPLACE FUNCTION mf_Upper(
      p_Input VARCHAR2
  ) RETURN VARCHAR2
IS
  PRAGMA UDF;
BEGIN
  RETURN UPPER(p_Input);
END;
/

SELECT mf_Upper('test') AS upper_dummy
  FROM dual
;