1

I've been using the Oracle ODP.NET, Managed Driver to issue queries from C#.

There were many queries i wrote that took forever to return any data; like it was ignoring my row limiting clauses:

SELECT TOP 10 * 
FROM Customers

or

SELECT * 
FROM Customers
FETCH FIRST 10 ROWS ONLY

It was like Oracle was ignoring my 10 row limit, and started returning everything - because it was taking forever to return. Eventually i realized that it wasn't that the query was busy returning a lot of data; it was that the query SQL was invalid.

When you issue invalid SQL to Oracle, it will not fail with an error.

  • select top 10 * from customers;
  • select 'Hello, world!';
  • select sysdate from duel;
  • select sysdate fro;
  • Ceilings make pie and markers drive the ocean with their feet

Oracle will sit there waiting for the command timeout to expire.

IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = "Ceilings make pie and markers drive the ocean with their feet";

IDataReader rdr = cmd.ExecuteReader(); //<--will wait forever

while (rdr.Read()) 
{
   //...
}

I eventually realized that the Oracle.ManagedDataAccess.Client.OracleCommand object (bizzarly) has no CommandTimeout; it defaults to running forever. Once i changed it to a more reasonable 30 seconds, i start seeing the error message:

ORA-12537: Network Session: End of file

Is there an option in the Oracle ODP.NET, Managed Driver, or in the connection string, or in the OracleCommand to tell the server to throw an error if there is an error in my SQL statement?

Bonus Chatter

Oracle provides two database drivers for ADO.net

  • one is an ADO.net managed wrapper around the 100MB native database drivers that you must already have installed (Oracle.DataAccess)
  • the other is a standalone 3.8 MB driver, written in pure managed code, that has no other dependencies (Oracle.ManagedDataAccess)

Microsoft had created an Oracle driver, which is included in the .NET Framework. But that driver is deprecated, and no longer functions in .NET 4.5 requires the full Oracle client software version 8.1.7 or greater to be installed (System.Data.OracleClient)

Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
  • You don't mention it, are you getting back data in a reasonable time with valid SQL? – Shannon Severance Dec 11 '15 at 21:10
  • Odd, I can't reproduce this. The behaviour you are asking for (throw an exception if the SQL is invalid) is the behaviour I would expect of the driver, and is the behaviour I saw with all five of your invalid queries. Do you get the same behaviour when you run invalid queries against the same database using another program, such as SQL*Plus? – Luke Woodward Dec 11 '15 at 21:10
  • @ShannonSeverance Yes i do. `SELECT 1 FROM Dual` returns instantly. – Ian Boyd Dec 11 '15 at 21:26
  • Do you have access to DB, ie. V$SESSION and v$sql? Which statement arrives the DB? See http://dba.stackexchange.com/questions/81/how-to-find-the-latest-sql-statements-within-the-database – Wernfried Domscheit Dec 11 '15 at 21:48
  • @WernfriedDomscheit Unfortunately i don't have access to anything on the server; it's someone else's server, and i'm only issuing my read-only queries. – Ian Boyd Dec 11 '15 at 22:05

0 Answers0