"WITH" Statement in SQL containing a UNION Returns Incorrect Result

(Doc ID 725992.1)

Last updated on JULY 05, 2017

Applies to:

Oracle Data Provider for .NET - Version: 10.2.0.2.21 to 11.1.0.7.0

Symptoms

A SQL statement using the "WITH" clause containing a  "UNION ALL"
returns NULL values instead of the correct results.

The same SQL statement in SQL*Plus returns the correct values.

The problem also occurs using Microsoft .Net data provider for Oracle.

An example of the failing SQL is:

WITH e AS ( Select EMPNO From EMP Where DEPTNO = 20 Order By MGR ) Select E.EMPNO From E Union All Select E.EMPNO From DUAL, E Order By EMPNO

Output from SQL*Plus

  EMPNO
-----------
7369
7369
7566
7566
7788
7788
7876
7876
7902
7902

Output from ODP.Net 10.2.0.2.20 and 11.1.0.7.0.

EMPNO
-----------
7369
7566
7788
7876
7902
<Null>
<Null>
<Null>
<Null>

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