Home » Infrastructure » Windows » case sensitive column names in data reader (odac,2.111.7.20, windowsXP)
case sensitive column names in data reader [message #482620] Sun, 14 November 2010 04:06 Go to next message
julb
Messages: 17
Registered: November 2010
Junior Member
Hi,

i have a very bizzare situation - it seems that oracledatareader on some client machine is case sensitive.
We are using oracle 10g.
2 client machines connect to the same DB and run the same code - one works and another doesn't.
they are both using odac 2.111.7.20.

this is my .NET code (which works fine on every environment it was installed in the last 2 years):

using (OracleConnection sqlConn = new OracleConnection())
{
sqlConn.ConnectionString = connectionString;
using (OracleCommand sqlCmd = new OracleCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "getValues";
sqlCmd.Connection = sqlConn;
sqlCmd.Parameters.Add("v_ret", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
sqlConn.Open();

using (OracleDataReader dr = sqlCmd.ExecuteReader())
{
while (dr.Read()) {
switch (dr["Name"].ToString())
{
case "value1":
.....
}
}
}
}
}

this is the oracle procedure:


PROCEDURE getValues(
rc IN OUT sys_refcursor
)
IS
BEGIN
OPEN rc FOR
SELECT Name, Value FROM MySchema.MyTable;
END;

The problem that we saw was that on one of the client machines dr["Name"] threw exception :
Error Source: Oracle.DataAccess.Client.OracleDataReader.GetOrdinal
Error Message: System.IndexOutOfRangeException: Unable to find specified column in result set
at Oracle.DataAccess.Client.OracleDataReader.GetOrdinal(String name)
at Oracle.DataAccess.Client.OracleDataReader.get_Item(String columnName)


We modified the code and used NAME instead of Name and it worked!! (dr["NAME"] instead of dr["Name"])
How can it be?
from oracle documentation:
A case-sensitive search is made to locate the specified column by its name. If this fails, then a case-insensitive search is made.

My question is what can cause such a behavior?
Some ODAC/ODP definitions, .NET configuration?

Thanks.
Re: case sensitive column names in data reader [message #482623 is a reply to message #482620] Sun, 14 November 2010 10:13 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The following doesn't have to do anything with your case (as I've never even heard of "oracledatareader"), but - here's how it goes. Oracle, by default, stores column names (actually, all objects' names) in uppercase unless you tell it not to. How to say it? Using double quotes around a name.

Here's an example:
SQL> create table test (name varchar2(20), "Address" varchar2(20));

Table created.

SQL> select count(*) from user_tab_columns
  2  where table_name = 'TEST';

  COUNT(*)
----------
         2

SQL> select count(*) from user_tab_columns
  2  where table_name = 'TEST'
  3    and column_name = 'NAME';

  COUNT(*)
----------
         1

SQL> select count(*) from user_tab_columns
  2  where table_name = 'TEST'
  3    and column_name = 'ADDRESS';

  COUNT(*)
----------
         0

SQL> select count(*) from user_tab_columns
  2  where table_name = 'TEST'
  3    and column_name = 'Address';

  COUNT(*)
----------
         1

Funny things that happen - every time you actually use that (mixed case) column name, you have to enclose it into double quotes:
SQL> insert into test (name, address) values ('Little', 'Croatia');
insert into test (name, address) values ('Little', 'Croatia')
                        *
ERROR at line 1:
ORA-00904: "ADDRESS": invalid identifier


SQL> insert into test (name, "Address") values ('Little', 'Croatia');

1 row created.

SQL>


Now, I don't know whether you (or someone else) created a table using mixed case. If so, the above example explains it. If not, well, you'd rather wait for another opinion (from someone who actually knows what the whole issue is about).
Re: case sensitive column names in data reader [message #482643 is a reply to message #482623] Mon, 15 November 2010 03:16 Go to previous messageGo to next message
julb
Messages: 17
Registered: November 2010
Junior Member
thanks for trying.
you need to understand what odp and oracledatareader are, otherwise it doesn't help.
Re: case sensitive column names in data reader [message #482664 is a reply to message #482643] Mon, 15 November 2010 04:37 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right; I was afraid that my efforts would be useless. Sorry.
Re: case sensitive column names in data reader [message #482819 is a reply to message #482620] Tue, 16 November 2010 00:41 Go to previous message
julb
Messages: 17
Registered: November 2010
Junior Member
I found what the problem was
www.west-wind.com/Weblog/posts/2145.aspx

Apparently in Turkish localization upper case I is not the same as lower case i.
So if my resultset from db has column named "ID", and in my application i would try to access this column using "id", it won't find it.
Previous Topic: Installation of ORACLE 9i & 10g
Next Topic: The gateway could not receive a timely response
Goto Forum:
  


Current Time: Thu Mar 28 16:27:49 CDT 2024