Oracle包函数测试报错：ORA-00902与PLS-00382问题排查求助

咱们一步一步来排查你的问题，两个报错其实是由不同的问题导致的，咱们逐个分析并给出修复方案：

一、ORA-00902: 无效数据类型（直接用SQL语句调用函数报错） #

Oracle的SQL引擎无法直接识别包内定义的PL/SQL自定义集合类型（也就是你包里的t_detalle）。SQL只能使用通过CREATE TYPE语句在数据库级别创建的全局集合类型，而包内的类型属于PL/SQL专属的局部类型，不能在SQL查询中直接引用。所以你直接在SELECT语句里调用返回t_detalle的函数，SQL不知道这个类型是什么，就会抛出无效数据类型的错误。

二、PLS-00382: 表达式类型错误（PL/SQL块赋值报错） #

这个错误是因为你的函数实现里有好几处逻辑和类型匹配的问题：

• 函数缺少RETURN语句：你的f_totalizar_detalle函数声明了返回t_detalle，但函数体里根本没有RETURN v_detalle;语句，PL/SQL无法正确返回结果，类型匹配自然出问题。
• 游标列顺序与记录类型不匹配：c_datos游标查询的列顺序是SUM(d.total_doc), 'D', r.cod_rubro，但你的r_registro记录类型顺序是rubro_contable, tipo, monto，顺序完全反过来了，fetch时必然类型不匹配。
• c_datos的循环没有退出条件：你用了无限LOOP处理c_datos，没有EXIT WHEN c_datos%NOTFOUND;，会导致游标耗尽后抛出NO_DATA_FOUND错误，而且每次都往v_detalle(1)赋值，逻辑完全错误。
• 全局变量i的污染问题：你在包体里定义了全局的i integer :=1;，多次调用函数时i的值不会重置，会导致集合索引混乱。

修复后的代码 #

修复后的包体（不需要SQL调用的情况）

CREATE OR REPLACE PACKAGE BODY pkg_contabilidad AS
 FUNCTION f_totalizar_detalle(p_clave NUMBER) RETURN t_detalle IS
  v_detalle t_detalle;
  i integer := 1; -- 把i改为函数局部变量，避免全局污染
  CURSOR c_facturado IS
   SELECT c.cod_rubro, 'H' AS tipo, 
          CASE WHEN SUM(d.gravada)=0 THEN SUM(d.iva) ELSE SUM(d.gravada) END AS monto
   FROM fn_documentos_det d
   JOIN fn_conceptos c ON d.cod_concepto = c.cod_concepto
   WHERE d.clave_doc=p_clave
   GROUP BY c.cod_rubro;
  CURSOR c_datos IS
   SELECT r.cod_rubro AS rubro_contable, 'D' AS tipo, SUM(d.total_doc) AS monto
   FROM fn_documentos d
   JOIN fn_cajas_ctas r ON d.num_caja_cta = r.num_caja_cta
   WHERE d.clave_doc = p_clave
   GROUP BY r.cod_rubro;
 BEGIN
  -- 用FOR循环处理游标，自动管理打开/关闭/退出
  FOR fila IN c_datos LOOP
    v_detalle(i) := fila;
    i := i + 1;
  END LOOP;

  FOR fila IN c_facturado LOOP
    v_detalle(i) := fila;
    i := i + 1;
  END LOOP;

  -- 必须添加返回语句
  RETURN v_detalle;
 END f_totalizar_detalle;
END PKG_CONTABILIDAD;
/

如果需要在SQL中调用函数

先创建全局的类型（SQL能识别）：

-- 创建全局记录类型（替换XX为CN_RUBROS_CONTABLES.COD_RUBRO的实际长度）
CREATE OR REPLACE TYPE r_registro_global AS OBJECT (
  rubro_contable VARCHAR2(XX),
  tipo VARCHAR2(1),
  monto NUMBER(16)
);
/

-- 创建全局集合类型
CREATE OR REPLACE TYPE t_detalle_global AS TABLE OF r_registro_global;
/

然后修改包定义里的类型为全局类型，函数返回t_detalle_global，之后就可以用SQL查询：

SELECT * FROM TABLE(pkg_contabilidad.f_totalizar_detalle(100));

测试PL/SQL块示例 #

DECLARE
  v_result pkg_contabilidad.t_detalle;
BEGIN
  v_result := pkg_contabilidad.f_totalizar_detalle(100);
  -- 循环打印结果验证
  FOR idx IN v_result.FIRST..v_result.LAST LOOP
    DBMS_OUTPUT.PUT_LINE('Rubro: ' || v_result(idx).rubro_contable || 
                         ', Tipo: ' || v_result(idx).tipo || 
                         ', Monto: ' || v_result(idx).monto);
  END LOOP;
END;
/

内容的提问来源于stack exchange，提问作者Joaquin Gavilan Rojas