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


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)


Tim... said...


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

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.

Scott Wesley said...

Never mind - SQL Developer does not know what to do WITH this statement, pun intended.

Tim... said...

Scott: There are a number of clients that won't recognise it yet, including PL/SQL itself. :)



DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

Add your comments here.
(max 400 chars - plain text)