Empty record returned with this query when querying Oracle database with .NET but gave rows in SQL+

awedaonline

Hello guys; The code below returns an empty records but gave records from SQL+.

DbProviderFactory factory = DbProviderFactories.GetFactory("Oracle.DataAccess.Client");
DbConnection connection = factory.CreateConnection();

connection.ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS\_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Simplex-IT-02)(PORT=1521)))
                     (CONNECT\_DATA=(SERVER=DEDICATED)(SERVICE\_NAME=Simplex))); User Id=User\_Test;Password=password;";

DataTable table = new DataTable();

DbCommand command = connection.CreateCommand();
command.Connection = connection;
command.CommandText = "SELECT text FROM user\_views WHERE (view\_name='ACCOUNT\_BALANCES\_BY\_PERIOD');";

DbDataReader dataReader = command.ExecuteReader();

table.Load(dataReader);
dataReader.Close();

Meanwhile, when I replaced the command.CommandText with "SELECT DISTINCT view_name FROM user_views" it gave me list of all available views for the given schema. Why can't I get the sql text of a given view? Thank you in anticipation.

Simon_Whale

Just a suggestion what if you give the "text" column name an alias? Oracle Column name as alias example[^]

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

awedaonline

Simon_Whale wrote:

Just a suggestion what if you give the "text" column name an alias?

Thank you for the suggestion but I have already done that and it didn't work.

Simon_Whale

Have you read this one? https://forums.oracle.com/forums/thread.jspa?threadID=258409[^]

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Lost User

This could be related to ANSI Code Settings. Ray Yagubyan

awedaonline

Simon_Whale wrote:

https://forums.oracle.com/forums/thread.jspa?threadID=258409[^]

Thank you for the reference. But I tried it and still didn't get it working.

jschell

awedaonline wrote:

The code below returns an empty records but gave records from SQL+.

That isn't clear. Are you saying it returned zero records or that that returns some records but there was no data in the records? What values are returned by the following properties? dataReader.FieldCount dataReader.HasRows What is the data type of 'text' in the database?

PIEBALDconsult

My first thought is to remove the semi-colon from the end of the statement. Many ADO.net providers don't like executing a batch of statements. P.S. I get the following when I try to execute "SELECT * FROM dual;" Oracle.DataAccess.Client.OracleException: ORA-00911: invalid character I hope your classes aren't swallowing Exceptions.