Empty DataReader when SQL statement contains a join

65350fd4-571b-4896-b003-707aa958290d

I am using Oracle.ManagedDataAccess.Core v2.18.6 and I am experiencing an issue when executing a command that includes joins to other tables. I've tried different join syntaxes, etc., and have confirmed joining any table causes the datareader to be empty (.HasRows = false)
I even took the exact same statements and ran them in Oracle SQL Developer and rows come back. What am I missing?

string oradb = "connection string info here";
OracleConnection conn = new OracleConnection(oradb); // C#
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;

//if I select from one table the data reader contains data

cmd.CommandText = "select * from patients where personal_num=1";

//if I select from parent and child table data reader does not contain data
cmd.CommandText = "select a.* from patients a inner join patient_history b on a.personal_num = b.personal_num where personal_num=1"

cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
label1.Text = dr.GetString(0);
conn.Dispose();

65350fd4-571b-4896-b003-707aa958290d

Interestingly... the reason results were showing in SQL developer is I had just ran an insert into the history table but did not commit. The subsequent select must have been reading the uncommitted records. The .NET client was getting 0 rows back.

UGH! I am a SQL Server guy used to implicit transactions.

My apologies, thanks for the response!

AndrewSayer

65350fd4-571b-4896-b003-707aa958290d wrote:I am using Oracle.ManagedDataAccess.Core v2.18.6 and I am experiencing an issue when executing a command that includes joins to other tables. I've tried different join syntaxes, etc., and have confirmed joining any table causes the datareader to be empty (.HasRows = false) 
I even took the exact same statements and ran them in Oracle SQL Developer and rows come back. What am I missing?string oradb = "connection string info here";
OracleConnection conn = new OracleConnection(oradb); // C#
conn.Open(); 
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;

//if I select from one table the data reader contains datacmd.CommandText = "select * from patients where personal_num=1";
//if I select from parent and child table data reader does not contain data
cmd.CommandText = "select a.* from patients a inner join patient_history b on a.personal_num = b.personal_num where personal_num=1"cmd.CommandType = CommandType.Text; 
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
label1.Text = dr.GetString(0);
conn.Dispose();

Personal_num in your query could refer to either patients or patient_history tables so I would expect this query would fail to parse and you should get an exception. Are you sure you’ve run the exact same query in sql Developer?

65350fd4-571b-4896-b003-707aa958290d

Yes, I fully qualified it but neglected to reflect that in my post. No exception is being thrown. The datareader I get back is empty. All other properties are fine... The only difference it the SQL is a terminating semi-colon. I do not use it from the oracle data client.

65350fd4-571b-4896-b003-707aa958290d

Interestingly... the reason results were showing in SQL developer is I had just ran an insert into the history table but did not commit. The subsequent select must have been reading the uncommitted records. The .NET client was getting 0 rows back.

UGH! I am a SQL Server guy used to implicit transactions.

My apologies, thanks for the response!

AndrewSayer

65350fd4-571b-4896-b003-707aa958290d wrote:Interestingly... the reason results were showing in SQL developer is I had just ran an insert into the history table but did not commit. The subsequent select must have been reading the uncommitted records. The .NET client was getting 0 rows back.UGH! I am a SQL Server guy used to implicit transactions.My apologies, thanks for the response!

Implicit transactions aren’t so necessary when writers can’t block readers and readers cant block writers