[.NET] Oracle Client Exception - ORA-01722

最近的项目使用Oracle DB。在.NET里可以使用Oracle Client，来对Oracle DB下SQL指令。写了下面的程序代码，用来取得User数据表里的User数据。

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

using (OracleConnection connection = new OracleConnection(connectionString)) {     connection.Open();     using (OracleCommand command = new OracleCommand())     {         command.Connection = connection;         command.Parameters.Add(":lastName", "Chou");         command.Parameters.Add(":firstName", "Clark");         command.CommandText = @"SELECT * FROM USER_DATA WHERE FIRST_NAME=:firstName AND LAST_NAME=:lastName";           using (OracleDataReader reader = command.ExecuteReader())         {             while (reader.Read())             {                 // XXXXXXX             }             connection.Close();         }     } }

 

快速打完、按下执行。却产生了让人摸不清头绪的错误例外「ORA-01722:无效的数字」。

 

 

搞了半天才发现使用OracleCommand.Parameters来加参数是有顺序性的。在OracleCommand.CommandText里参数出现的顺序，必须要和OracleCommand.Parameters是一致的。也就是下列的程序代码才能正常执行：

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

using (OracleConnection connection = new OracleConnection(connectionString)) {     connection.Open();     using (OracleCommand command = new OracleCommand())     {         command.Connection = connection;         command.Parameters.Add(":firstName", "Clark");         command.Parameters.Add(":lastName", "Chou");                command.CommandText = @"SELECT * FROM USER_DATA WHERE FIRST_NAME=:firstName AND LAST_NAME=:lastName";           using (OracleDataReader reader = command.ExecuteReader())         {             while (reader.Read())             {                 // XXXXXXX             }             connection.Close();         }     } }

 

在网络上与朋友讨论这个问题与解法。经由Demo的提点，才知道原来只要将OracleCommand.BindByName设定为True就可以解决这个问题。范例程序如下：

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

using (OracleConnection connection = new OracleConnection(connectionString)) {     connection.Open();     using (OracleCommand command = new OracleCommand())     {         command.BindByName = true;         command.Connection = connection;         command.Parameters.Add(":lastName", "Chou");         command.Parameters.Add(":firstName", "Clark");         command.CommandText = @"SELECT * FROM USER_DATA WHERE FIRST_NAME=:firstName AND LAST_NAME=:lastName";           using (OracleDataReader reader = command.ExecuteReader())         {             while (reader.Read())             {                 // XXXXXXX             }             connection.Close();         }     } }

 

趁还有印象快速纪录一篇下来，为自己做个纪录，也希望能帮助到有需要的开发人员。 当然也要再次感谢Demo大大。:D