传递 int 的参数化查询列表会返回错误 ora-01722
使用 oledb 提供程序。查询是这样的：
SELECT appid
FROM table
WHERE response_id IN (?)
我获取一个 int 数组并将其发送到一个方法，该方法在数组值之间添加逗号分隔符，并返回一个字符串。然后将该字符串作为参数发送。
如果我有一个值要传递，那么这种方法可以正常工作，但是当我发送两个值时，我会收到 ORA-01722 错误。
我尝试查看 v_$sql 表来查看正在执行的内容，但它没有显示我的页面执行的查询。我只能看到通过蟾蜍执行的操作，即使我在这两种情况下使用相同的登录名。不确定是否还有其他表存储sql数据。
字符串生成器如下。
public string intArrayToString(int[] array)
{
if (array != null)
{
string delimiter = ",";
if (array.Length > 0)
{
StringBuilder builder = new StringBuilder();
builder.Append(array[0]);
for (int i = 1; i < array.Length; i++)
{
builder.Append(delimiter);
builder.Append(array[i]);
}
return builder.ToString();
}
else
{
return string.Empty;
}
}
else
{
return null;
}
}
Using the oledb provider. Query is something like this:
SELECT appid
FROM table
WHERE response_id IN (?)
I take an int array and send it to a method that adds a comma delimiter between the array values, and returns a string. This string is then sent as the parameter.
This works fine if I have one value to pass through, but when I send two values I get the ORA-01722 error.
I've tried looking at the v_$sql table to see what's being executed, but it's not showing queries executed by my page. I can only see things I executed via toad, even though I'm using the same login in both cases. Not sure if there are other tables that store sql data.
The string builder is below.
public string intArrayToString(int[] array)
{
if (array != null)
{
string delimiter = ",";
if (array.Length > 0)
{
StringBuilder builder = new StringBuilder();
builder.Append(array[0]);
for (int i = 1; i < array.Length; i++)
{
builder.Append(delimiter);
builder.Append(array[i]);
}
return builder.ToString();
}
else
{
return string.Empty;
}
}
else
{
return null;
}
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
不幸的是，您不能只将逗号分隔的字符串作为
IN值。您可以做的是自动为每个数组元素生成一个绑定变量并绑定每个值，如下所示：You cannot just put a comma-separated string as the
INvalue unfortunately. What you can do is automatically generating a bind variable for each of the array elements and binding each value, like so:您使用哪个驱动程序连接到 Oracle？这里有两个不同的 odp.net 解决方案： http://forums.oracle .com/forums/thread.jspa?threadID=892457&tstart=810
编辑：我看到您使用 oledb 提供程序。我想这限制了可能性？ （我从未使用过该提供商，所以我不知道）。也许是时候切换了？
Which driver to you use to connect to Oracle? Here two different odp.net solutions: http://forums.oracle.com/forums/thread.jspa?threadID=892457&tstart=810
edit: I see that you use the oledb provider. I guess that limits the possibilities? (I've never used that provider so I don't know). Maybe it is time to switch?