My Oracle Support Banner

WITH Clause Query Fails With ORA-32031 (Doc ID 852140.1)

Last updated on NOVEMBER 04, 2019

Applies to:

PL/SQL - Version 11.1.0.7 and later
Information in this document applies to any platform.
***Checked for relevance on 22-Jan-2014***

Symptoms

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.

Reference:

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10002.htm#i2077142

 

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.

Cause

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
Symptoms
Cause
Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.