NVARCHAR and the Oracle string aggregate function to perform concatenation
Thursday, December 31st, 2009My colleague Janice had a lot of fun while trying to concat NVARCHAR in oracle and she happens to share her findings with us. Hopefully, this will be helpful for someone
Just fyi…
I needed to create a string aggregate function for concatenation in Oracle. For example, given a user table where each record contains DepartmentCode and UserID, I would like to roll up the multiple UserIDs into one field with comma, etc separators.
| DepartmentCode | Users |
| 0 | User1, user2, user3, User4, User5 |
I found this nice summary of the problem along with six(6) potential solutions: http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
Bottom line…these techniques worked when the data involved was VARCHAR2 (1-byte char), . When I tried using NVARCHAR2 (multi-byte chars), I could not get all of the techniques to work. Here are my findings…
________________________________________
1. WMSYS.WM_CONCAT – This seems to be a slightly undocumented built-in function. Who knew? I am uncertain if it is fully supported by Oracle or not.
VARCHAR2: (Works!)
NVARCHAR2: (Doesn’t work. Returns nulls.)
2. Specific function – Needs a custom function for each string you might want to aggregate.
VARCHAR2: (Works!)
NVARCHAR2: (Works!)
3. Generic function with ref cursor – Only needs one function added. Usage in query is easy to understand.
VARCHAR2: (Works!)
NVARCHAR2: (Doesn’t work. Returns nulls.)
4. User defined aggregate function – Needs a type, type body, and function added. Support code is not obvious. Usage in query is easy to understand.
VARCHAR2: (Works!)
NVARCHAR2: (Doesn’t work. Returns nulls.) Note that when I changed the VARCHAR2 types to NVARCHAR2 in the sample code, I received a runtime error.
5. ROW_NUMBER() and SYS_CONNECT_BY_PATH functions – Needs nothing added. Query usage is by far the most complicated, but it certainly works.
VARCHAR2: (Works!)
NVARCHAR2: (Works!)
6. COLLECT function – Needs a type and a function added. Query usage is easy to understand.
VARCHAR2: (Works!)
NVARCHAR2: (Doesn’t work. Returns nulls.) Note that when I changed the VARCHAR2 types to NVARCHAR2 in the sample code, I received a runtime error.
________________________________________
NOTE: In all of the NVARCHAR2 examples that didn’t work, I tried a variety of explicit type conversions, TO_CHAR, TRANSLATE USING, etc. and nothing helped.
