Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ODP.NET Managed Driver - Query syntax bug

2898641Mar 23 2015 — edited Mar 24 2015

Greetings,

We recently converted an app of ours to the new(ish) Managed driver. All worked well, with very minor changes needed. However, we did notice one of our queries was returning 0 results with the managed driver while the unmanaged driver returned the correct number of results. The issue seemed to boil down to the use of parentheses and the UNION operator. When parentheses were used to surround each subquery that was being unioned, the managed driver returned 0 rows. When the parentheses were removed, the managed driver returned the correct results. A reproducible example is below:

var badQuery = @"

(SELECT 3 FROM DUAL)

UNION

(SELECT 2 FROM DUAL)

";

var goodQuery = @"

SELECT 3 FROM DUAL

UNION

SELECT 2 FROM DUAL

";

using (var conn = new OracleConnection(connString))

{

  conn.Open();

  var cmd = conn.CreateCommand();

  cmd.CommandType = CommandType.Text;

  cmd.CommandText = badQuery; // change query here

  using (var reader = cmd.ExecuteReader())

  {

    var table = new DataTable();

    table.Load(reader, LoadOption.OverwriteChanges);

    table.Rows.Count.Dump(); // returns 2 rows with goodQuery, 0 rows with bad query

  }

}

This seems odd to me, given that both queries should be proper SQL. Any ideas why the so-called badQuery doesn't work?

This post has been answered by Alex Keh-Oracle on Mar 23 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 21 2015
Added on Mar 23 2015
2 comments
798 views