Using WMSYS.WM_CONCAT with Oracle XE 10g

Question

When I try to use WMSYS.WM_CONCAT with Oracle XE 10g, I receive a compilation error: ORA-00904: "WMSYS"."WM_CONCAT": invalid identifier. Can anyone verify that this is indeed due to XE lacking this (undocumented) feature? If so, is there anyway to enable it in XE?

Answer 1

I had found a couple reference sites, but had no luck enabling it. I ended up writing my own function to handle the concatenation.

CREATE or replace FUNCTION CONCAT_LIST( cur SYS_REFCURSOR, sep Varchar2 ) RETURN  VARCHAR2 IS
ret VARCHAR2(32000);
tmp VARCHAR2(4000);
BEGIN
loop
  fetch cur into tmp;
  exit when cur%NOTFOUND;
    if ret is null then
       ret := tmp;
    else
      ret := ret || sep || tmp;
    end if;

end loop;
RETURN ret; END;/

Then it can be called as

SELECT distinct CONCAT_LIST(CURSOR(SELECT id FROM test_table1), ',') test_table1 FROM dual