OracleDataAdapter is not filling DataTable from Query

Question

I am writing an C# application for both SQL Server and Oracle Databases that is defined in my application configuration file. When I run the code in the SQL Server environment, it works fine. I get the correct results.

The working SQL Server Code is here:

sqlConn.Open();
stmt = "SELECT ACTION_ID, ACTION FROM DB.ACTIONS WHERE ACTION_DATE < GETDATE() AND STATUS = 'Pending'";
SqlDataAdapter sqlAdapt = new SqlDataAdapter(stmt, sqlConn);
sqlAdapt.Fill(dt);
sqlConn.Close();

The Oracle Code that is not working is as follows:

oraConn.Open();
stmt = "SELECT ACTION_ID, ACTION FROM DB.ACTIONS WHERE ACTION_DATE < SYSDATE AND STATUS = 'Pending'";
OracleDataAdapter oraAdapt = new OracleDataAdapter(stmt, oraConn);
oraAdapt.Fill(dt);
oraConn.Close();

I'm trying to fill the datatable dt with the results. I have tried using a DataSet first and filling the DataTable with the DataSet, but that didn't work in Oracle when it did in SQL Server. It seems like there's something simple missing.

I'm not getting any errors, I'm just getting a 0 result set from Oracle even though I know there's data there to be pulled.

Answer 1

The issue was the DBO account I was logging into the database with did not have permissions to the table. I ran a simple grant and it worked great.

    GRANT SELECT ON DB.ACTIONS TO USER;

Answer 2

Run query directly on the DB (for example using sqlplus, TOAD, SQLDEveloper etc.). Does it return anything? If not:

a) In SQL Server text comparison (STATUS = 'Pending') is case insensitive in Oracle it is case sensitive. Maybe you have data in status column like 'PENDING' for example ?

b) Is ACTION_DATE is date datatype? If it is varchar it won't work.

Hope it helps.