I wasn't really sure about the usefulness of Subquery Functions until I had to work in an environment where I had SELECT but no EXECUTE. This was difficult to troubleshoot complex SQL which did things like call PL/SQL functions and TABLE type casts. That's when I saw the light. However, it didn't take me long to hit ORA-1008s.
Narrowed it down to a simple test case:
>>> with_binds.sql select banner from v$version; variable a VARCHAR2(10) variable b VARCHAR2(10) variable c VARCHAR2(10) begin :a := 'A'; :b := 'B'; :c := 'C'; end; / prompt "All good" WITH FUNCTION is_f RETURN VARCHAR2 IS BEGIN RETURN 'Y'; END; select 'X' from dual where :a = 'A' and :b = 'B' / prompt "Going to throw an ORA-01008" WITH FUNCTION is_f RETURN VARCHAR2 IS BEGIN RETURN 'Y'; END; select 'X' from dual where (:a = 'A' or :a = 'D') and :b = 'B' / prompt "Going to throw an ORA-3113" WITH FUNCTION is_f RETURN VARCHAR2 IS BEGIN RETURN 'Y'; END; select 'X' from dual where (:a = 'A' or :a = 'D') /
Output:
SQL> @with_binds BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production PL/SQL Release 12.1.0.2.0 - Production CORE 12.1.0.2.0 Production TNS for Linux: Version 12.1.0.2.0 - Production NLSRTL Version 12.1.0.2.0 - Production PL/SQL procedure successfully completed. "All good" ' - X "Going to throw an ORA-01008" WITH * ERROR at line 1: ORA-01008: not all variables bound "Going to throw an ORA-3113" WITH * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 106651 Session ID: 947 Serial number: 19762
So, what's going on? It appears that internally, bind order is not persisted. A bug was filed, however, the comment in the SR was "development is not likely to work on this bug anytime soon". And since internet searches seem to come up nil, I might be the only one attempting to use this feature? Or at least with bind variables.
Bug 27092158 - SUBQUERY FACTORING WITH FUNCTION RESULTS IN ORA-1008 / ORA-7445 W/ DUP VARIABLE
Update: Tested with same results in 18c - 2019-02-13