Until Oracle 12c, we have been using WITH Clause to replace
- 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.