Forums | Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

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)

Cheers
Tomasz

Tim... said...

Hi.

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. :)

Cheers

Tim...

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
...
SELECT slow_function(1)
FROM all_ones
WHERE ROWNUM

Denis Krep said...

PS: Sorry, somehow part of message got trimmed during posting.
Remaining part of message was
...
10 rows selected.
Elapsed: 00:00:01.01

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.
Prepared Statement Error: Table './oraclebasecms/cms_page_comment_uuids' is marked as crashed and should be repaired