Unable to query Oracle database via C# API

Question

Good morning.

I am attempting to connect to an Oracle database I have set up. before I go into detail, here's the code:

//string was slightly altered.
string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=name)));User Id = system; Password = mypass; ";    
string toReturn = "D.BUG-";    
using (OracleConnection oracleConnection = new OracleConnection(connectionString))    
{    
    oracleConnection.Open();    
    using (OracleCommand oracleCommand = new OracleCommand())    
    {    
        oracleCommand.Connection = oracleConnection;    
        oracleCommand.CommandText = "SELECT lixo FROM lixeira WHERE lixo IS NOT NULL";    
        oracleCommand.CommandType = CommandType.Text;    
        using (OracleDataReader oracleDataReader = oracleCommand.ExecuteReader())    
        {     
            //This point IS reached!
            while (oracleDataReader.Read())    
                //This point is never reached...
                toReturn += oracleDataReader.GetString(0);    
        }    
    }    
}    
return toReturn;

Now, I know for a fact that connecting works, and I know for a fact that the table "lixeira" can be found; I have tested this by changing that name to another name, and getting the corresponding "i can't find that table" exception.

'ORA-00942: tabela ou visualização não existe'. (Table or View does not exist)

The issue is that this code is unable to read. The same query ran through SQL Developer works: SQL Developer screenshot of the same query

So, I'm kinda at a loss as to why oracleDataReader.Read() just never works. Am I doing something wrong?

The table exists but it doesn't exist... strange, have you checked that the user you log in from c# (in you connection string) has the needed permissions? — D Ie
– D Ie, Commented Aug 14, 2017 at 9:31
....Yup. That's it. You can post this as answer, I'll accept it. Thank you very much. — Cloud
– Cloud, Commented Aug 14, 2017 at 10:21

D Ie · Accepted Answer · 2017-08-14 10:28:47Z

1

Make sure your user/password in the connection string is the correct one.

If a table doesn't exist but exists... it probably doesn't exist for your current user (= that user has not the necessary permissions)

answered Aug 14, 2017 at 10:28

D Ie

8417 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abhishek Dixit · Accepted Answer · 2017-08-14 12:57:20Z

0

Previous answer is correct, I am just adding another bit. can you replace your query as following :

SELECT lixo FROM <table owner>.lixeira WHERE lixo IS NOT NULL

This will give more appropriate error of what you are missing. Its probably a permission (grant select) issue.

Abhi

answered Aug 14, 2017 at 12:57

Abhishek Dixit

394 bronze badges

2 Comments

Cloud Over a year ago

I have personally confirmed this. Thank you. As I created a new user and went ahead to test it, the error was much, much clearer. 'ORA-01045: user C##CLOUD lacks CREATE SESSION privilege; logon denied'.

Cloud Over a year ago

And, upon correcting that, but revoking SELECT permission from C##CLOUD, the error reported was: 'ORA-01031: privilégios insuficientes'. (Insufficient privileges). Which is a far more debug-friendly error.

Collectives™ on Stack Overflow

Unable to query Oracle database via C# API

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related