WITH Clause Query Fails With ORA-32031
(Doc ID 852140.1)
Last updated on NOVEMBER 04, 2019
Applies to:PL/SQL - Version 184.108.40.206 and later
Information in this document applies to any platform.
***Checked for relevance on 22-Jan-2014***
Starting with Oracle versions 9i and onward, the "WITH" clause was introduced to allow Subquery Factoring.
The WITH clause can help improve performance and also readability by enabling a "SELECT statement to define the subquery block at the start of the query, process the block just once, label the results, and then refer to the results multiple times.
With early versions like 11.1.0.x, a limitation was present in that the subquery does not support recursion e.g. referencing the subquery alias from inside the subquery.
With 11.2 and onward, recursive subquery factoring is supported.
The technique seen below is used within other vendor databases such as Microsoft SQL Server and it is known as CTE -Common Table Expression.
The following are results when connected to an 11.1 database.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document