关于collect函数的疑惑

显示全部楼层 · 2015-7-28 15:35:34

本帖最后由 liuzhe_521 于 2016-5-8 01:57 编辑
有如下sql：
select deptno,
cast(collect(ename) over(order by deptno) as sys.odcivarchar2list)
from emp;
执行报错：
ORA-03113:通信通道的文件结尾
ORA-03114: 未连接到 ORALCE
以下两种写法执行正常：
select deptno,
cast(collect(ename) over(order by 1) as sys.odcivarchar2list)
from emp;

select deptno,
cast(collect(ename) over(partition by deptno) as sys.odcivarchar2list)
from emp;
下面的改写也会报同样错误：
select deptno,
cast(collect(ename) over(order by 1 rows between unbounded preceding and current row) as sys.odcivarchar2list)
from emp;
多台机器上试过都是同样情况，请问这是什么原因，或者是oracle的BUG？

千问 · 2015-7-28 15:35:34

这是个BUG。
但是你也太有创意了，竟然想到把它当作分析函数来用，一般都是用作聚合函数。

千问 · 2015-7-28 15:35:34

newkid 发表于 2016-5-8 17:49
这是个BUG。
但是你也太有创意了，竟然想到把它当作分析函数来用，一般都是用作聚合函数。
cast(collect(ename) over() as sys.odcivarchar2list)
和
cast(collect(ename) over(partition by deptno) as sys.odcivarchar2list)
是没错的，看来应该支持分析函数才对，应该是个BUG。。

千问 · 2015-7-28 15:35:34

newkid 发表于 2016-5-8 17:49
这是个BUG。
但是你也太有创意了，竟然想到把它当作分析函数来用，一般都是用作聚合函数。
第一次见到collect,上网查才知道10g就有了

千问 · 2015-7-28 15:35:34

SQL> with t as(select 'a'ename,1 deptno from dual union all select 'b'ename,2 deptno from dual)
2select deptno,
3cast(collect(ename) over(order by deptno) as sys.odcivarchar2list)from t;
cast(collect(ename) over(order by deptno) as sys.odcivarchar2list)from t
*
第 3 行出现错误:
ORA-00932: 数据类型不一致: 应为 -, 但却获得 -

已用时间:00: 00: 00.92
SQL> show rel
release 1102000200

千问 · 2015-7-28 15:35:34

〇〇发表于 2016-5-8 20:58
SQL> with t as(select 'a'ename,1 deptno from dual union all select 'b'ename,2 deptno from dual)
2 ...
构造T表的时候ename要转换一下：
with t as
(select cast('a' as varchar2(50)) ename, 1 deptno
from dual
union all
select 'b' ename, 2 deptno
from dual)
select deptno,
cast(collect(ename) over(order by deptno) as sys.odcivarchar2list)
from t;

千问 · 2015-7-28 15:35:34

liuzhe_521 发表于 2016-5-9 09:09
构造T表的时候ename要转换一下：
with t as
(select cast('a' as varchar2(50)) ename, 1 deptno
ERROR:
ORA-29400: 数据插件错误
ORA-00600: 内部错误代码, 参数: [kokbCollTerminate], [14], [], [], [], [], [], [], [], [], [], []

未选定行
已用时间:00: 00: 11.13
SQL> show rel
release 1102000200

千问 · 2015-7-28 15:35:34

〇〇发表于 2016-5-9 09:38
ERROR:
ORA-29400: 数据插件错误
ORA-00600: 内部错误代码, 参数: [kokbCollTerminate], [14], [], [], ...
就是这个问题，只是我这里报错不一样。
select deptno,
cast(collect(ename) over(order by 1) as sys.odcivarchar2list)
from emp;

select deptno,
cast(collect(ename) over(partition by deptno) as sys.odcivarchar2list)
from emp;
这两种写法是没问题的。

千问 · 2015-7-28 15:35:34

学习了 collect在列转行上跟 listagg很相似