Creating a aggregation function on Oracle 10g returning unhelpful error

I've got some help and was led to this page and this explanation, which should contain a efficient way to aggregate things.

It suggest to use de COLLECT function and some other custom things. I'm trying get along with it, but the error mes开发者_StackOverflow中文版sages (plus my newbness) aren't the most helpful.

The function:

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;

And my tests:

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

Would give me an ORA-06553: PLS-306: wrong number or types of arguments in call to 'TAB_TO_STRING'

This is pretty much copy-and-past from the source I mention in the beginning, and the function makes sense for me.. what am I missing?

thanks!

[EDIT] Codo has figured that one of the problems was Oracle understanding the 'a' as a char, rather than varchar. This brought the question to the real issue. I updated it so it is focused.

---

For reasons I don't really understand, Oracle thinks that the PRODCODE column of your synthetic table isn't a VARCHAR2 column. If you slightly modify one of the PRODCODE values, it'll work:

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