4

I currently am working on a project within Crystal Reports that refuses to use the undocumented function WM_CONCAT, which is allowable within Oracle 10g. Here is the WM_CONCAT header information 

WM_CONCAT(p1 IN VARCHAR2) RETURN VARCHAR2

To use WM_CONCAT I pass it the following: WM_CONCAT(column1); This function seems to accept a column of type varchar2, and returns a comma delimited list of values from the column. I currently have a custom version of this function that works (on my work computer), but it is not optimal and lacks re-usability. Could anyone provide a good, re-usable function like WM_CONCAT that I could use?

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
contactmatt
  • 18,116
  • 40
  • 128
  • 186

2 Answers2

8

Do you get an error message when you use wm_concat? Unlike functions like to_char, it is owned by wmsys and you might need to use wmsys.wm_concat to use it. (unless you create the necessary synonyms of course).

Now for the actual question,

This technique is called string aggregation.

You could find a lot of other alternatives here.

http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php For other methods, Search for "stragg" on http://asktom.oracle.com Another useful link : http://www.orafaq.com/node/2290

This is probably the most used one. A lot of teams write their own custom functions which more or less do the same.

CREATE OR REPLACE FUNCTION get_employees (p_deptno  in  emp.deptno%TYPE)
  RETURN VARCHAR2
IS
  l_text  VARCHAR2(32767) := NULL;
BEGIN
  FOR cur_rec IN (SELECT ename FROM emp WHERE deptno = p_deptno) LOOP
    l_text := l_text || ',' || cur_rec.ename;
  END LOOP;
  RETURN LTRIM(l_text, ',');
END;
/
SHOW ERRORS

while this solution works for varchar2 and number, the best generic solution can be built using Oracle ODCIAggregate interface.

http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10800/dciaggfns.htm#sthref462

Implementation for the same is at the first link above at www.oracle-base.com

Rajesh Chamarthi
  • 18,568
  • 4
  • 40
  • 67
  • Yea, I tried using wm_concat prefixed with wmsys, but that still doesn't work. Thanks for the code above, but I'm more looking for a GENERIC function that could be used independent from joins or where clauses within the function itself. – contactmatt Dec 28 '09 at 16:31
  • This is a very delayed answer, but when it came down to it I wrote a method like the one above since writing a "generic" method took too much time in my current project. – contactmatt Jan 06 '12 at 20:58
1

I've solved this using a technique similar to the last one in the oracle-base article: define a custom TABLE type and write a function to aggregate a value of that type into a string. I called my function joinstr and then you can call it as follows:

SELECT joinstr(CAST(MULTISET(SELECT column1 FROM table1) AS my_string_table_type), ',') 
FROM DUAL

Note: I was on 9i until recently and haven't looked into COLLECT yet.

Dan
  • 10,990
  • 7
  • 51
  • 80