Oracle Select 与数组绑定

oracle select binded with an array

提问人:semper fi 提问时间:9/19/2023 更新时间:9/20/2023 访问量:90

问:

有一个非常简单的查询,可以在 oracle 数据库端完美运行,但尝试使用 .net 实现它并在下面遇到以下问题,以下是代码示例:

var cmd = new OracleCommand
{
    CommandText = "SELECT * FROM TESTTABLE WHERE ID IN (:1)"
};

cmd.ArrayBindCount = values.Length; // an array of integers boxed to object[]

cmd.Parameters.Add(new OracleParameter
    {
        OracleDbType = OracleDbType.Array,
        Value = values // object array
    });

var reader = await cmd.ExecuteReaderAsync();

使用这种方法,我有一个例外 - 如果我替换到,那么我有以下异常 - .Invalid parameter bindingOracleDbTypeOracleDbType = OracleDbType.DecimalORA-03146: invalid buffer length for TTC field

请帮我确定我在这里缺少什么?

C# SQL .NET Oracle

评论

0赞 Yasin ARLI 9/20/2023
你能确保你的值数组是一个整数数组吗?
0赞 semper fi 9/20/2023
@YasinARLI在这里创建帖子之前这样做了,所以我也尝试直接传递一个整数数组,例如,5 个值和 5 作为我在 ArrayBindCount 中指定的计数,与上面提到的问题相同。

答:

1赞 MT0 9/20/2023 #1

请帮我确定我在这里缺少什么?

SELECT * FROM TESTTABLE WHERE ID IN (1, 2, 3)

会起作用。

SELECT * FROM TESTTABLE WHERE ID IN ( :bind_variable_containing_collection_type )

将不起作用,因为:

  1. 绑定变量包含单个值(无论该单个值是标量还是包含数组的 UDT),该子句会将左操作数与每个右操作数进行比较,以查看它们是否相等;由于子句中只有一个值,因此您正在检查一个数字是否等于一个数组,这将导致语法错误,因为它们不是相同的数据类型(如果它可以工作,但它不起作用,那么无论该数字是否是数组的成员,数字值都不会等于数组值)。ININ
  2. C# 不支持将数组传递给 Oracle。您可以传递关联数组,但由于某种未知原因,它不支持非关联数组。
  3. 在 Oracle 中,关联数组是一种 PL/SQL 数据类型,这意味着它们可以在 PL/SQL 函数、过程或匿名块中使用,但不能在 SQL 查询中使用。

您需要做的是将 PL/SQL 关联数组传递给一些 PL/SQL 代码,该代码将关联数组转换为非关联数组,然后在查询中使用该数组。

有关传递 PL/SQL 关联数组的 Oracle 端示例,请参阅如何在“where in”子句中使用关联数组?通过 C# 将整数数组传递给 Oracle 过程

评论

1赞 semper fi 9/20/2023
但是,如果您尝试从 datagrip 执行此查询,并且出于某种原因它会起作用,所以我不能同意第一点,那么当您尝试使用批量更新或创建时,第二点它还支持非关联数组。SELECT * FROM TESTTABLE WHERE ID IN ( : bind_variable_containing_collection_type )
0赞 MT0 9/20/2023
@semperfi 从 Oracle 的角度来看,绑定变量始终是一个值,我所说的是正确的。如果你有一个 SQL 驱动程序,在将语句传输到数据库之前将语句转换为,那么 Oracle 将只看到它收到的语句,其中包含多个绑定参数(每个参数都是一个标量值),但这将是 SQL 驱动程序的一个函数(由于你的问题没有说明任何驱动程序详细信息,因此我们无法对此发表评论)。WHERE id IN (:array)WHERE id IN (?, ?, ?, ?)