Oracle 10gR2中通过sys_refcursor向JasperReports传嵌套表数据问题

我刚好碰到过类似的老版本Oracle+JasperReports的适配难题，来给你梳理下整个过程和最终可行的方案：

问题背景 #

你需要把PL/SQL中对同一张表多列过滤统计后的键值对（键是报表列类型过滤结果，值是统计数值），通过sys_refcursor传给JasperReports 3.6.1，而且因为过滤限制没法用子查询，环境是Oracle 10gR2且所有组件都不能升级。

最初尝试：关联数组直接转换失败 #

你一开始用关联数组存储键值对，代码如下：

create or replace PROCEDURE test_proc02(test_cursor OUT sys_refcursor) IS 
/* associative arrays declaration */ 
TYPE transfer_type IS TABLE OF NUMBER INDEX BY VARCHAR2(10); 
transfer_table transfer_type;

但这里有个核心问题：关联数组是PL/SQL级别的私有类型，无法直接在SQL语句中通过TABLE()函数转换为结果集，所以你尝试的Select * from table(cast(transfer_table AS transfer_type))根本行不通。

尝试嵌套表转换：触发PLS-00382错误 #

于是你想到把关联数组复制到嵌套表，代码如下：

/* nested table declaration */ 
TYPE transfer_nt_type IS TABLE OF VARCHAR2(20); 
/* nested table initilization */ 
transfer_nt transfer_nt_type := transfer_nt_type(); 
/* some variables */ 
transfer_id VARCHAR2(10); 
transfer_number NUMBER; 
nt_counter INTEGER := 0; 
nt_iter VARCHAR2(10); 
/* copying AA into NT */ 
nt_iter := transfer_table.FIRST; 
WHILE (nt_iter IS NOT NULL) LOOP 
nt_counter := nt_counter+1; 
transfer_nt.EXTEND; 
transfer_nt(nt_counter):=transfer_table(nt_iter); 
dbms_output.put_line('nested table ('||nt_counter||'): '||transfer_nt(nt_counter)); 
nt_iter := transfer_table.NEXT(nt_iter); 
END LOOP;

然后尝试把嵌套表传给游标时触发了PLS-00382: expression is of wrong type错误：

/* Trying to send NT to JR */ 
OPEN travelCursor FOR SELECT * FROM TABLE(cast(transfer_nt AS transfer_nt_type)); 

这个错误的原因有两个：

1. 即使你在schema级别声明了嵌套表类型，Oracle 10g要求在SQL中使用TABLE()函数的类型必须是数据库级别的对象类型（而非PL/SQL块内定义的类型）；
2. 你的嵌套表只存储了值，丢失了键的信息，就算转换成功也没法满足报表需要的键值对结构。

你后来尝试的管道函数方案，本质上也卡在了类型兼容的问题上，所以会报同样的错误。

最终可行方案：全局临时表（GTT） #

最后你用全局临时表（GTT）完美解决了问题，这也是老版本Oracle中传递自定义结果集最稳妥的方式：

1. 创建全局临时表 #

CREATE GLOBAL TEMPORARY TABLE transfer_temp ( 
 id VARCHAR(20), 
 value NUMBER, 
 month NUMBER 
) ON COMMIT PRESERVE ROWS;

这里的ON COMMIT PRESERVE ROWS很关键，它确保事务提交后临时表的数据不会被清空，正好适配JasperReports读取游标的场景。

2. 在存储过程中插入数据并返回游标 #

nt_iter := transfer_table.FIRST; 
WHILE (nt_iter IS NOT NULL) LOOP 
nt_counter := nt_counter+1; 
INSERT INTO transfer_temp VALUES(nt_iter,transfer_table(nt_iter),06); 
nt_iter := transfer_table.NEXT(nt_iter); 
END LOOP; 
OPEN test_cursor FOR select * from transfer_temp order by transfer_temp.id;

这个方案的优势：

• 全局临时表是数据库级别的对象，SQL可以直接查询，完全适配sys_refcursor的需求；
• 完整保留了键（id）和值（value）的对应关系，正好匹配你提供的示例数据结构：

• Oracle 10g对GTT的支持非常稳定，不会有任何类型兼容问题，完美适配不能升级的老版本环境。

内容的提问来源于stack exchange，提问作者Rafa J