如何调整LISTAGG在选择查询中支持超过4000个字符？ [英] How to tweak LISTAGG to support more than 4000 character in select query?

问题描述

Oracle数据库11g企业版11.2.0.2.0 - 64位生产。

我有一个以下格式的表。

 名称部门
 Johny Dep1 
 Jacky Dep2 
 Ramu Dep1 
  

我需要一个以下格式的输出。

  Johny，Ramu 
 Dep2  -  Jacky 
  

我试过了'LISTAGG'是一个硬限制为4000个字符。因为我的db表是巨大的，这不能在应用程序中使用。另一个选项是使用

  SELECT CAST（COLLECT（Name）
  / pre> 
 
 
但是我的框架允许我执行只选择查询和没有PL / SQL脚本。我没有找到任何方式使用CREATE TYPE命令
 
 
 
有没有其他方法可以使用select查询实现上述结果？
解决方案
如果你不能创建类型（你不能只使用sql * plus创建一个一个关闭），但你可以使用COLLECT，在数组中有几个在RDBMS中敲响，运行这个查询：
  select owner，type_name，coll_type，elem_type_name，upper_bound， length 
 from all_coll_types 
其中elem_type_name ='VARCHAR2'; 
  
 db，我可以使用 sys.DBMSOUTPUT_LINESARRAY 这是一个相当大的varray。
  select department，
 cast（collect（name）as sys.DBMSOUTPUT_LINESARRAY）
 from emp 
 group by department; 
  
 
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production.

I have a table in the below format.
Name     Department
Johny    Dep1
Jacky    Dep2
Ramu     Dep1
I need an output in the below format.
Dep1 - Johny,Ramu
Dep2 - Jacky
I have tried the 'LISTAGG' function, but there is a hard limit of 4000 characters. Since my db table is huge, this cannot be used in the app. The other option is to use the
SELECT CAST(COLLECT(Name)
But my framework allows me to execute only select queries and no PL/SQL scripts.Hence i dont find any way to create a type using "CREATE TYPE" command which is required for the COLLECT command. 

Is there any alternate way to achieve the above result using select query ?
解决方案
if you cant create types (you can't just use sql*plus to create on as a one off?), but you're OK with COLLECT, then use a built-in array. There's several knocking around in the RDBMS. run this query:
select owner, type_name, coll_type, elem_type_name, upper_bound, length 
 from all_coll_types
 where elem_type_name = 'VARCHAR2';
e.g. on my db, I can use sys.DBMSOUTPUT_LINESARRAY which is a varray of considerable size.
select department, 
       cast(collect(name) as sys.DBMSOUTPUT_LINESARRAY) 
  from emp 
 group by department;


                        
这篇关于如何调整LISTAGG在选择查询中支持超过4000个字符？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！