Select fails with ODP, works with OraOLEDB & Microsoft's Oracle Provider

25489

The following:

using System;
using System.Data;
using System.Text;
using Oracle.DataAccess.Client;

namespace Test
{
class Test
{
static void Main()
{
string connStr = "Data Source=xxx;User ID=yyy;Password=zzz";
OraConnection oraConn = new OraConnection(connStr);
DataSet myDS = new DataSet("ODPTest");
string tableName = "USER_TAB_COLUMNS";
string sql = "select TABLE_NAME, COLUMN_NAME, DATA_TYPE from USER_TAB_COLUMNS";
OraDataAdapter oraDA = new OraDataAdapter(sql, oraConn);
TimeSpan bgnTS = new TimeSpan(DateTime.Now.Ticks);
oraDA.Fill(myDS, tableName);
TimeSpan endTS = new TimeSpan(DateTime.Now.Ticks);
Console.WriteLine(endTS.Subtract(bgnTS).ToString());
Console.WriteLine("USER_TAB_COLUMNS rows: " + myDS.Tables[0].Rows.Count.ToString());
oraDA.Dispose();
oraConn.Close();

oraConn = new OraConnection(connStr);
myDS = new DataSet("ODPTest");
StringBuilder sqlSB = new StringBuilder("select c1, c2, c3 from (");
sqlSB.Append("select TABLE_NAME as c1, COLUMN_NAME as c2, DATA_TYPE as c3 ");
sqlSB.Append("from USER_TAB_COLUMNS ");
sqlSB.Append("order by 1, 2");
sqlSB.Append(") where rownum <= 20");
sqlSB.Append(" order by 1, 2");
oraDA = new OraDataAdapter(sqlSB.ToString(), oraConn);
bgnTS = new TimeSpan(DateTime.Now.Ticks);

try
{
oraDA.Fill(myDS, tableName);
}
catch (OraException e)
{
Console.WriteLine("");
Console.WriteLine("exception sql: " + oraDA.SelectCommand.CommandText);
Console.WriteLine("OraException.Procedure: " + e.Procedure);
Console.WriteLine("OraException.Source: " + e.Source);
Console.WriteLine("");

foreach (OraError oe in e.Errors)
{
Console.WriteLine("OraError.DataSource: " + oe.DataSource);
Console.WriteLine("OraError.Message: " + oe.Message);
Console.WriteLine("OraError.Number: " + oe.Number);
Console.WriteLine("OraError.Procedure: " + oe.Procedure);
Console.WriteLine("OraError.Source: " + oe.Source);
}

return;
}

endTS = new TimeSpan(DateTime.Now.Ticks);
Console.WriteLine(endTS.Subtract(bgnTS).ToString());
oraDA.Dispose();
oraConn.Close();
}
}
}

Returns:

00:00:03.2950337
USER_TAB_COLUMNS rows: 13361

exception sql: select c1, c2, c3 from (select TABLE_NAME as c1, COLUMN_NAME as c
2, DATA_TYPE as c3 from USER_TAB_COLUMNS order by 1, 2) where rownum <= 20 order
by 1, 2
OraException.Procedure: dvlp.cabana
OraException.Source: Oracle Data Provider for .NET

OraError.DataSource:
OraError.Message:
OraError.Number: -3002
OraError.Procedure: dvlp.cabana
OraError.Source: Oracle Data Provider for .NET
Press any key to continue


I'm connecting to an Oracle 9i instance running on W2K.
The code is running on a seperate W2K workstation with 9iR2 client installed
along with the OraOLEDB and Microsoft's production Oracle .NET data provider.

The select statement that fails with ODP works with SQL*Plus, OraOLEDB & Microsoft's provider.
Am I doing something wrong here or is this a problem with the provider?

182042

This issue has been identified as a bug with the nested SELECT statements and the fix would be available in the next release.

170397

Looks it doesn't support inline view. The following failed:

select * from (select * from dual)