开发者

Oracle wm_concat query returns result set with odd characters

开发者 https://www.devze.com 2023-01-07 12:47 出处:网络
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.

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;
0

精彩评论

暂无评论...
验证码 换一张
取 消