case sensitive column names in data reader [message #482620] |
Sun, 14 November 2010 04:06 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 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 #482819 is a reply to message #482620] |
Tue, 16 November 2010 00:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
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.
|
|
|