My Oracle query produces the correct result set, but data is being presented with odd characters as you can see by the blocks in the picture below.
alt text http://lh3.ggpht.com/_VSEi5_hEznA/TDtsnM9HDnI/AAAAAAAAAD8/zoEzbEaKB9s/blocks.jpg
Any reason that you can think of as to why it would do this and what these characters actually are? Below is the query I'm using. Thanks in advance.
SELECT wmsys.wm_concat(userFirstName) AS firstNames
FROM COURSESECTION
JOIN CLASSCOORDINATOR on classcoord_sectionId = coursesect_sectionId
JOIN usr_USER on classcoord_coordinatorId = userId
GROUP BY classcoord_sectionId;
If I use the same query but use a 开发者_开发技巧dump(wmsys.wm_concat(columnName)) then I get the data presented in the picture below.
alt text http://lh3.ggpht.com/_VSEi5_hEznA/TDx2dle4BmI/AAAAAAAAAEM/cP6opWer-Go/concat.jpg
DUMP is my starting point when investigating character set issues.
select wmsys.wm_concat('êõôó'), dump(wmsys.wm_concat('êõôó'))
from dual connect by level < 3;
In my database (AL32UTF8 as shown by querying the NLS_CHARACTERSET parameter from v$nls_parameters), this returns as expected.
How does selecting a simple userFirstName show up ?
I'd suspect that the values have come from a multi-character set environment but have somehow been treated as single character set data.
Edited to add:
I'm able to reproduce this using the following:
select * from v$nls_parameters
where parameter = 'NLS_NCHAR_CHARACTERSET';
AL16UTF16
create table t (v nvarchar2(3));
insert into t values ('a');
select dump(v) from t;
select wmsys.wm_concat(v), v, dump(v), dump(wmsys.wm_concat(v)) from t
group by v;
To remedy, you could try casting the string to a standard VARCHAR2
select wmsys.wm_concat(cast(v as varchar2(3)))
from t
group by v;
精彩评论