Following SQL statement returns data when executed in Oracle SQL Developer:

SELECT TC_GUID FROM TBLBUF WHERE TC_DEST = 'aaaaaaa' AND TC_STATE <= 20

but when it is executed in a following C# code, reader returns no rows.

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

namespace OracleQuery
{
    public class Program
    {
        public static DbDataReader QueryReader()
        {
            OracleConnection connection = new OracleConnection(".........");
            connection.Open();
            using (OracleCommand command = connection.CreateCommand())
            {
                command.InitialLOBFetchSize = -1;
                command.InitialLONGFetchSize = -1;
                ((IDbCommand)command).Transaction = null;
                command.CommandText = "SELECT TC_GUID FROM TBLBUF WHERE TC_DEST = 'aaaaaaa' AND TC_STATE <= 20";
                command.CommandType = CommandType.Text;
                return command.ExecuteReader(CommandBehavior.Default | CommandBehavior.CloseConnection);
            }
        }

        public static void Main(string[] args)
        {
            DbDataReader reader = QueryReader();
            while (reader.Read())
            {
                string s = (string)reader["TC_GUID"];
                Console.WriteLine(s);
            }
        }
    }
}

When I leave only one part of WHERE clause, either TC_DEST = 'aaaaaaa' or TC_STATE <= 20, it returns rows.

What could be a reason for such behaviour?

CodePudding user response：

What could be a reason for such behaviour?

Rows which have been INSERTed but not COMMITted are only visible inside the session in which they were created. Therefore, if you have created some new rows but have not issued a COMMIT command in the SQL Developer session, you will not be able to see that uncommitted data from any other session (even if you connect as the same user, as it will create a different session).

If this is the the case, the solution would be to COMMIT the data in the SQL Developer session and it would then be visible to other sessions.