Oracle InvalidOperationException - When trying to select from a table

Question

I've got a parameters table where I've got a parameter to say whether my program should run, I'm trying to get this value to check for a function.

Here is the function

    private static bool shouldRun()
    {
        OracleCommand c = conn.CreateCommand();

        c.CommandText = "select value from parameters where lower(name) = lower('valuetocheck')"; // this one doesn't work
        //c.CommandText = "select 'Y' from dual"; - This one works
        c.CommandType = System.Data.CommandType.Text;

        OracleDataReader dr = c.ExecuteReader();

        dr.Read();

        string s = dr.GetString(0); // exception on this line

        return false;
    }

The additional information is below:

Invalid operation on a closed object

I've tried selecting from dual, as you can see above, which works perfectly fine, but when I try to run the actual query it doesn't like it. I've checked the query in SQL Developer and it works fine.

I've had a look around SO and other websites and the only information I could get on this issue was that the command wasn't associated with a connection, which as you can see above it is.

I've also tried just doing

OracleCommand c;
c.Connection = conn;

Which also doesn't work.

Any insight into this would be great, thanks!

Answer 1

I had the same issue recently. I got the "Invalid Operation on a closed object" but when I debug the code, it was the OracleDataReader that was closed and not the OracleConnection. My problem was the query retuned an empty response and the DataReader closes itself if there is no data. So my resolution was simply checking if the data reader have any rows by adding the following..

dro.Read();

if (dro.HasRows)
{
   //Do your 
}

I hope it helps someone.

Answer 2

When Oracle gives this error - "Invalid Operation on a closed object", more often than not, what actually happens is that the user does not have access privileges to the objects they are trying to access in the database. Check your connection string and make absolutely sure that the user listed in that connection string does have privileges to the objects in your query - in this case, read access to the Parameters table.