在 Oracle 10g 上创建聚合函数返回无用的错误

Question

我得到了一些帮助，并被引导至此页面并且这个解释，它应该包含一种有效的聚合方式。

它建议使用 de COLLECT 函数和其他一些自定义的东西。我正在尝试适应它，但错误消息（加上我的新手）并不是最有帮助的。

函数：

CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);

CREATE OR REPLACE FUNCTION tab_to_string (
    p_varchar2_tab  IN  t_varchar2_tab, 
    p_delimiter     IN  VARCHAR2 DEFAULT ',')
RETURN VARCHAR2 IS
  l_string     VARCHAR2(32767);
BEGIN
  FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
    IF i != p_varchar2_tab.FIRST THEN
      l_string := l_string || p_delimiter;
    END IF;
    l_string := l_string || p_varchar2_tab(i);
  END LOOP;
  RETURN l_string;
END tab_to_string;

我的测试：

with my_table as
(
    select 'user1' as usrid, 'ab' as prodcode from dual union
    select 'user1' as usrid, 'bb' as prodcode from dual union
    select 'user1' as usrid, 'a' as prodcode from dual union
    select 'user2' as usrid, 'db' as prodcode from dual union
    select 'user2' as usrid, 'b' as prodcode from dual union
    select 'user2' as usrid, 'bfdd' as prodcode from dual
)
select
    usrid, 
    tab_to_string(CAST(COLLECT(prodcode) AS t_varchar2_tab)) AS codes
from
    my_table
group by
    usrid

会给我一个 ORA-06553: PLS-306: error number or types of argument in call to 'TAB_TO_STRING'

这几乎是从源代码复制过去的我在一开始就提到了，这个功能对我来说很有意义……我错过了什么？

谢谢！

[编辑] Codo 发现问题之一是 Oracle 将 'a' 理解为 char，而不是 varchar< /em>.这就把问题引向了真正的问题。我更新了它，所以它是集中的。

Answer 1

由于我不太明白的原因，Oracle 认为合成表的 PRODCODE 列不是 VARCHAR2 列。如果您稍微修改 PRODCODE 值之一，它将起作用：

with my_table as
(
    select 'user1' as usrid, 'ab' as prodcode from dual union
    select 'user1' as usrid, 'b' as prodcode from dual union
    select 'user1' as usrid, 'c' as prodcode from dual union
    select 'user2' as usrid, 'd' as prodcode from dual union
    select 'user2' as usrid, 'e' as prodcode from dual union
    select 'user2' as usrid, 'f' as prodcode from dual
)
select
    usrid, 
    tab_to_string(CAST(COLLECT(prodcode) AS t_varchar2_tab)) AS codes
from
    my_table
group by
    usrid