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

Last updated on JULY 05, 2017

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.

1 WITH TAB1 AS (
2 SELECT COL1 FROM TAB1
3 )
4* SELECT * FROM TAB1
5 /
SELECT COL1 FROM TAB1
*
ERROR at line 2:
ORA-32031: illegal reference of a query name in WITH clause

 

Another restriction found is that Oracle  does not support column aliases and the WITH clause

The example below shows the error attempting to use "column1" as an alias.

  1  WITH TAB1 (column1) AS (
  2    SELECT COL1 FROM TAB1
  3  )
  4* SELECT * FROM TAB1
SQL> /
WITH TAB1 (column1) AS (
          *
ERROR at line 1:
ORA-32033: unsupported column aliasing

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms