|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.
Scott Wesley said...Never mind - SQL Developer 18.104.22.168 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. :)
Lalit Kumar B said...Very nice article. A quick review and I found this:
CREATE TABLE t1 AS
SELECT 1 AS id
CONNECT BY level COMMIT;
You don't need the commit here ;-)
Tim... said...LOL. :)
Thanks for pointing that out. I've corrected it. :)
Lalit Kumar B said...Hi,
>Interestingly, the ";" does not seem to work as a terminator to the SQL statement when the PL/SQL declaration is included in the WITH clause. If we attempt to use it on its own, SQL*Plus waits for more text to be entered. Even the example in the SQL Reference manual uses a combination of ";" and "/".
I got in touch with the Principal technical writer and got the following feedback
Lalit Kumar B said...
SQL*Plus detects that you are entering a block of PL/SQL and you need to provide the slash to tell it that you are finished entering the block of PL/SQL.
Lalit Kumar B said...
There was a big debate a while back over whether the docs should show the slash in our examples, since it is a SQL*Plus thing. It was decided that we should to avoid many confused customers sitting there wondering why nothing was happening.... :-)
Lalit Kumar B said...Due to 400 characters limit, I had to break it in 3 comments. So, the above 3 comments are in continuation.
Yes. As you will see from my examples I use the "/" rather than the ";".
There are a couple of issues with this functionality that are yet to be resolved, including PL/SQL support.
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!