有一个数据库是oracle 9.2.0.1,现在遇到这样一个问题:先创建个函数如下:
CREATE OR REPLACE FUNCTION Test_Func
(
v_useraname in nvarchar2
)
return nvarchar2
is
v_Result nvarchar2(50);
begin
v_Result := v_useraname ||' welcome!'; return v_Result;
end ;
/执行如下Sql:select b.itemid,
Test_Func(itemid)
from
(
select * from (
select '001' as itemid from dual
union select '002' as itemid from dual
union select '003' as itemid from dual
) order by itemid desc
) b得到结果却是如下:
1 003 003 welcome!
2 002 welcome!
3 001 welcome!而在其他oracle库上,得到的结果都是:
1 003 003 welcome!
2 002 002 welcome!
3 001 001 welcome!
是不是数据库有什么问题,哪里有设置呢?哪位能帮我一下
CREATE OR REPLACE FUNCTION Test_Func
(
v_useraname in nvarchar2
)
return nvarchar2
is
v_Result nvarchar2(50);
begin
v_Result := v_useraname ||' welcome!'; return v_Result;
end ;
/执行如下Sql:select b.itemid,
Test_Func(itemid)
from
(
select * from (
select '001' as itemid from dual
union select '002' as itemid from dual
union select '003' as itemid from dual
) order by itemid desc
) b得到结果却是如下:
1 003 003 welcome!
2 002 welcome!
3 001 welcome!而在其他oracle库上,得到的结果都是:
1 003 003 welcome!
2 002 002 welcome!
3 001 001 welcome!
是不是数据库有什么问题,哪里有设置呢?哪位能帮我一下
结果不对的那个数据库,你别用工具执行,你就用SQL*PLUS连接上,执行一下看看
不行就用PL/SQL Developer调试吧。
(
v_useraname in varchar2
)
return varchar2
is
v_Result varchar2(50);
begin
v_Result := v_useraname ||' welcome!'; return v_Result;
end ;
/再执行,就正确了。 什么原因呢?
看一下成功与失败这两个数据的national character 设置是否有区别。
如果是不在同一个客户端测试,看看NLS_LANG 是否一致。
nls_langauge是否一样。
检查了字符集,没有什么问题,多谢你的提醒1 NLS_LANGUAGE SIMPLIFIED CHINESE
2 NLS_TERRITORY CHINA
3 NLS_CURRENCY ¥
4 NLS_ISO_CURRENCY CHINA
5 NLS_NUMERIC_CHARACTERS .,
6 NLS_CALENDAR GREGORIAN
7 NLS_DATE_FORMAT DD-MON-RR
8 NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
9 NLS_CHARACTERSET ZHS16GBK
10 NLS_SORT BINARY
11 NLS_TIME_FORMAT HH.MI.SSXFF AM
12 NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
13 NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
14 NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
15 NLS_DUAL_CURRENCY ¥
16 NLS_NCHAR_CHARACTERSET AL16UTF16
17 NLS_COMP BINARY
18 NLS_LENGTH_SEMANTICS BYTE
19 NLS_NCHAR_CONV_EXCP FALSE