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
精彩评论