Oracle.ManagedDataAccess.Core 2.XX to 3.21.100 GetColumnSchema throws with parameterized OFFSET

[EmilianoElMariachi]

The method OracleDataReader.GetColumnSchema() throws when the request contains a parameterized OFFSET without WHERE clause.

using var command = new OracleCommand
{
    Connection = connection,
    CommandType = CommandType.Text,
    BindByName = true,
    CommandText = "SELECT * FROM SOME_TABLE OFFSET :vOff ROWS FETCH NEXT :vMax ROWS ONLY"
};
command.Parameters.Add(new OracleParameter
{
    ParameterName = "vOff",
    Direction = ParameterDirection.Input,
    Value = 0
});
command.Parameters.Add(new OracleParameter
{
    ParameterName = "vMax",
    Direction = ParameterDirection.Input,
    Value = 10
});
using var r = command.ExecuteReader();
var s = r.GetColumnSchema();  //This line throws! Oracle.ManagedDataAccess.Client.OracleException : 'ORA-009422: table or view does not exist'

The following requests will pass:

• "SELECT * FROM SOME_TABLE WHERE 1=1 OFFSET :vOff ROWS FETCH NEXT :vMax ROWS ONLY" -- Useless WHERE clause
• "SELECT * FROM SOME_TABLE OFFSET 0 ROWS FETCH NEXT :vMax ROWS ONLY" -- Offset in query instead of param

I could reproduce the issue with any Oracle.ManagedDataAccess.Core from 2.xx to 3.21.100.

[alexkeh]

@EmilianoElMariachi What is the SQL query that has the error when GetColumnSchema is called?

You can usually find this out if you turn on ODP.NET tracing to TraceLevel 7. It will trace out all the queries, including internal metadata queries ODP.NET executes.

What is the analogous query in ODP.NET Core 2.x that is working?

[EmilianoElMariachi]

@alexkeh I already wrote an example of failing query, but again for example:

SELECT * FROM SOME_TABLE OFFSET :vOff ROWS FETCH NEXT :vMax ROWS ONLY

Note that SOME_TABLE is of course an existing table, for which I have grant access. I mean I can reproduce the issue whatever the table I'm using.

One thing that is very surprising from my experimentations is that if I change the query with a useless WHERE clause (WHERE 1=1), like in the example below:

SELECT * FROM SOME_TABLE WHERE 1=1 OFFSET :vOff ROWS FETCH NEXT :vMax ROWS ONLY

Then I don't have the problem anymore calling GetColumnSchema().

I invite you to copy/paste the full example of code from my first post to a fresh .NET Core Console project, referencing the latest Oracle.ManagedDataAccess.Core nuget and using the table of your choice, to observe the issue.

[alexkeh]

What I meant is GetColumnSchema() is making a query itself that causes the error. What is the SQL that method is executing? You can see all SQL queries it executes when you turn on tracing. The error should occur right before the problematic statement executes. And it would be useful to compare it with what is executed with ODP.NET Core 2.x.

What is your DB version?

[EmilianoElMariachi]

Database version:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

KO UseCase

Full csharp code:

using Oracle.ManagedDataAccess.Client;
using System.Data;
using System.Data.Common;

OracleConfiguration.TraceFileLocation = @"C:\temp\BugOracle";
OracleConfiguration.TraceLevel = 7;

using var connection = new OracleConnection("**************");

connection.Open();

using var command = new OracleCommand
{
    Connection = connection,
    CommandType = CommandType.Text,
    BindByName = true,
    CommandText = "SELECT * FROM UTI OFFSET :vOff ROWS FETCH NEXT :vMax ROWS ONLY"
};
command.Parameters.Add(new OracleParameter
{
    ParameterName = "vOff",
    Direction = ParameterDirection.Input,
    Value = 0
});
command.Parameters.Add(new OracleParameter
{
    ParameterName = "vMax",
    Direction = ParameterDirection.Input,
    Value = 10
});
using var r = command.ExecuteReader();
var s = r.GetColumnSchema();  //This line throws! Oracle.ManagedDataAccess.Client.OracleException : 'ORA-009422: table or view does not exist'

trc log file (only Cmd Text outputs):

2023-04-14 11:27:49.385447 TID:1   (PUB) (ENT) OracleCommandImpl.CheckForReturningClause() Cmd Text (SQL):  HasReturnClauseSearchKey : SELECT * FROM UTI OFFSET :vOff ROWS FETCH NEXT :vMax ROWS ONLY
Has Returning Clause: False
2023-04-14 11:27:49.506003 TID:1   (PUB) (ENT) OracleCommandImpl.RetrieveMetadata() Cmd Text (SQL): SELECT * FROM UTI
Has Returning Clause: False
2023-04-14 11:27:49.523707 TID:1   (PUB) (ENT) OracleCommandImpl.RetrieveMetadata() Cmd Text (SQL): SELECT * FROM OFFSET
Has Returning Clause: False

OK UseCase

Now if I change the SQL CommandText with:

SELECT * FROM UTI WHERE 1=1 OFFSET :vOff ROWS FETCH NEXT :vMax ROWS ONLY

trc log file (only Cmd Text outputs):

2023-04-14 11:47:04.340069 TID:1   (PUB) (ENT) OracleCommandImpl.CheckForReturningClause() Cmd Text (SQL):  HasReturnClauseSearchKey : SELECT * FROM UTI WHERE 1=1 OFFSET :vOff ROWS FETCH NEXT :vMax ROWS ONLY
Has Returning Clause: False

No exception is thrown and I have only one Cmd Text logged in trc file.

[alexkeh]

I was able to reproduce this bug. I've filed a bug report (35296986) for the dev team to review. Thanks for reporting!