|WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1) - Define PL/SQL functions and procedures in the WITH clause of SQL statements.|
Steven Feuerstein said...Excellent review of this new feature, Tim. Thanks! I look forward to leveraging your research in new PL/SQL Challenge quizzes!
Tomasz Lesinski said...So deterministic is working wrong in 12c for this particular feature. It's a bug.
I don't think you should publish bugs it confuses people you should rise SR instead ;o)
I know what you are saying, but I think it is more confusing to use a "performance" feature, then find out it reduces performance with no explanation. :)
Also, lack of support in static SQL within PL/SQL is a bug too. It is already raised. Do you think that's not important enough to tell people? I think that bug is very important! It confused the hell out of me. :)
Denis Krep said...Tim, thanks a lot for raising those bugs. Pretty sure you're right when pointing on pitfalls!
BTW, sometimes deterministic works even without subquery (but that's a different case - here it clearly see that parameter is always same):
WITH FUNCTION slow_function
Denis Krep said...PS: Sorry, somehow part of message got trimmed during posting.
Remaining part of message was
10 rows selected.
Bryan Grenn said...This is a great article, and I just presented this topic to an Oracle users group.
All developers thought this is the most fantistic thing they had seen. The biggest advantage was the ability to test new/changed functions themselves without creating them. This gets them around all the authorization issues that they often face.