Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE OR REPLACE FUNCTION FCN_SLOW
  RETURN NUMBER IS
BEGIN
  DBMS_SESSION.SLEEP(5); --5 seconds
  RETURN 0;
END FCN_SLOW;
/
SELECT * FROM USER_ERRORS;
SELECT A1 + A1
  FROM (SELECT FCN_SLOW () AS A1
          FROM DUAL)


A1+A1


0


SELECT A1 + A1
  FROM (SELECT FCN_SLOW () AS A1
          FROM DUAL
         WHERE ROWNUM >= 1)


A1+A1


0


WITH slow_query(a1) AS (
  SELECT /*+ materialize */
         FCN_SLOW ()
  FROM   DUAL
)
SELECT A1 + A1
  FROM slow_query


A1+A1


0