为什么我得到"ORA-00932:不一致的数据类型:预期-得到-"在准备好的语句中使用COLLECT()时? [英] Why do I get "ORA-00932: inconsistent datatypes: expected - got -" when using COLLECT() in a prepared statement?

问题描述

我将此查询与 Perl DBI 一起使用:

I am using this query with the Perl DBI:

SELECT c.change_id
     , COLLECT(t.tag) AS the_tags
  FROM changes   c
  LEFT JOIN tags t ON c.change_id = t.change_id
 WHERE c.project = ?
 GROUP BY c.change_id

DBI使用OCI来准备此语句，绑定我传递的值并获取结果.但是由于某种原因，Oracle不喜欢它.错误输出为:

The DBI uses OCI to prepare this statement, bind the value I pass, and get the results. But Oracle, for some reason, does not like it. The error output is:

     ORA-00932: inconsistent datatypes: expected - got - (DBD ERROR: error possibly near <*> indicator at char 41 in '
            SELECT c.change_id
                 , <*>COLLECT(t.tag) AS the_tags
              FROM changes   c
              LEFT JOIN tags t ON c.change_id = t.change_id
             WHERE c.project = :p1
             GROUP BY c.change_id
        '

信息不多.但是，我不仅可以通过将调用更改为COLLECT()，还可以通过将占位符替换为实际值来消除此错误:

Not very informative. However, I can make this error go away not only by changing the call to COLLECT() also by replacing the placeholder with the actual value:

     SELECT c.change_id
          , COLLECT(t.tag) AS the_tags
       FROM changes   c
       LEFT JOIN tags t ON c.change_id = t.change_id
      WHERE c.project = 'tryoracle'
      GROUP BY c.change_id

该版本运行良好. Oracle为什么不喜欢带有COLLECT()的预处理语句?

That version works perfectly. Why doesn't Oracle like the prepared statement with the COLLECT()?

如果有帮助，请此处是通过OCI提取的与OCI相关的呼叫的痕迹 ora_verbose = 6(h/t @bohica).

In case it's any help, here is a trace of the OCI-related calls extracted via ora_verbose = 6 (h/t @bohica).

推荐答案

最后，由于用户的一些挖掘，终于找到了解决方案.问题不在于占位符.为什么在VirtualBox映像上没有占位符的情况下仍能正常工作，我不知道.不，问题出在COLLECT().似乎收集的值都需要转换为特定类型 ，结果数组也需要转换为预定义的数组数据类型.碰巧我的代码具有自定义数组类型:

Finally got a solution to this issue, thanks to some digging by a user. The problem was not with the placeholder; why it worked without the placeholder on the VirtualBox image I have no idea. No, the issue was with the COLLECT(). Seems that both the values being collected need to be cast to a specific type, and the resulting array also needs to be cast to a pre-defined array data type. Just so happens that my code has a custom array type:

CREATE TYPE sqitch_array AS varray(1024) OF VARCHAR2(512);

因此，我可以通过像这样强制转换COLLECT()来使查询正常工作:

So I'm able to get the query to work by casting the COLLECT() like so:

CAST(COLLECT(CAST(t.tags as VARCHAR2(512))) AS sqitch_array)

这篇关于为什么我得到"ORA-00932:不一致的数据类型:预期-得到-"在准备好的语句中使用COLLECT()时?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！