8i | 9i | 10g | 11g | 12c | Misc | PL/SQL | SQL | RAC | Linux

Home » Misc » Here

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.

Scott Wesley said...

Never mind - SQL Developer 4.0.0.13 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. :)

Cheers

Tim...

Lalit Kumar B said...

Very nice article. A quick review and I found this:

Set Up:

CREATE TABLE t1 AS
SELECT 1 AS id
FROM dual
CONNECT BY level COMMIT;

You don't need the commit here ;-)

Tim... said...

LOL. :)

Thanks for pointing that out. I've corrected it. :)

Cheers

Tim...

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.

Tim... said...

Hi.

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.

Cheers

Tim...

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!