Context

I have two tables, FOO and BAR. Both have data. I'm using Dapper in a .NET 5 project. When I try to query FOO, I get zero results. When I query BAR with the same connection, I get results as expected. What's going on?

Example code

using var connection = GetConnection();
await connection.OpenAsync(cancellationToken);

// THIS RETURNS ZERO RESULTS
var foos = await connection.QueryAsync("SELECT * FROM FOO");

// THIS RETURNS RESULTS AS EXPECTED
var bars = await connection.QueryAsync("SELECT * FROM BAR");

// THIS THROWS AN EXCEPTION (because the table doesn't exist)
var throws = await connection.QueryAsync("SELECT * FROM DOES_NOT_EXIST");

Other thoughts

• I've confirmed my connection string is correct
• I've confirmed FOO exists in the DB defined by my connection string
• I've confirmed FOO has data
• I've confirmed I can query FOO successfully via Oracle SQL Developer
• FOO is a new table created in the past couple days

Does anybody know what's happening here?

Or what might be happening?

CodePudding user response：

Does anybody know what's happening here?

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.