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

Home » Misc » Here

Comments for WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1)


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

Helma said...

Simple note for anyone who likes to try this too.
I tried the "with_plsql hint" part in SQLDeveloper 4.0.3.16 and no matter what I did I kept keeping errors like "SQL command not properly ended".
In total despair I started good old SQLPLus (12.1.0.1.0and bingo first attempt worked as described.

Yuri said...

Hi Tim,
do you know what rights are must be granted to another user for selecting from a view with PL/SQL inside? And where it is documented?
As I found what is needed: 1) schema name before any table inside a function, 2) READ privilege on the view and 3) SELECT privilege on all tables inside the function.

Tim... said...

Hi.

The answer will vary depending on if the procedure using owner rights or invoker rights. As for where it is documented, I don't think I've seen a specific example of that mentioned in the docs, but what you've suggested doesn't sound unusual to me.

You need grant option if items are in different schemas.

Cheers

Tim...


Yuri said...

Sorry, Tim, I was not clear in my question. What I meant was a VIEW with pl/sql function or procedure defined in WITH clause of the VIEW query.

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!

If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.