Oracle 12c - With Clause Enhancements

5 Mins Blog

Until Oracle 12c, we have been using WITH Clause to replace  

• Sub-query
• Correlated Subqueries 

Now to this you can define PLSQL declarations in a WITH Clause statement from Oracle 12c. 
Blogged in the same chronological order,  I encountered errors 

In SQL-Developer

Look what i get 

Oops, let try with Sqlplus

                   

 Isn't ";" the terminator for the SQL Statement,  


how about Update 

Is update not supported ?

hmm, Ok how about inside a plsql

Are these restrictions ?

Lets see one by one, 

         WITH Clause with inline PLSQL are supported only in SQL Developer Version 4.0, check this link to see the number of bugs fixed in SQL Developer 4.0 

WITH Clause with inline PLSQL query from - Sql Developer Version 4.0

WITH Clause with PLSQL Declaration - "/" is the query terminator

Update - WITH Clause with PLSQL Declaration -

       From Oracle Documentation 'If the top-level statement is a DELETE, MERGE, INSERT, or UPDATE statement, then it must have the WITH_PLSQL hint", 

Note from Oracle Documentation:" Hint - /*+ WITH_PLSQL */  is not an optimizer hint, it is just to specify the WITH PLSQL Declaration clause within the statement "


WITH Clause with PLSQL Declaration inside a PLSQL Block
  
         You cannot execute WITH Clause with PLSQL Declaration directly inside a PLSQL Block, but can be executed as dynamic SQL

SQL's making use of functions, Functions !!!! which are yet not stored objects is the key benefit we get and can be of great use for one time data migration scripts, for which you really don't want write stored functions.

Thanks for reading, feel free to leave your comments. Let's see about performance benefits in the next post