ORA-00902无效数据类型错误排查：PL/SQL包插入表时异常

错误原因 #

你定义的PKG_PRODUCTS.t_filter是PL/SQL关联数组类型（带有INDEX BY BINARY_INTEGER），这种类型属于PL/SQL专属类型，仅能在PL/SQL上下文（比如变量赋值、循环遍历）中使用，无法直接在SQL语句的TABLE()函数中被SQL引擎识别。当你在INSERT语句的WHERE条件里使用SELECT * FROM TABLE(p_vendor)时，SQL引擎无法解析该类型，因此抛出ORA-00902: invalid datatype错误。

此前使用SELECT返回refcursor时功能正常，大概率是因为当时未在SQL语句中直接引用关联数组，而是通过PL/SQL逻辑（比如游标循环）处理过滤，避开了SQL引擎对关联数组的识别需求。

解决方案 #

提供两种可行的解决方式，可根据你的业务兼容性需求选择：

方案1：替换为SQL兼容的嵌套表类型 #

将PL/SQL关联数组替换为SQL级别的嵌套表类型（无INDEX BY），让SQL引擎能够直接识别：

1. 修改包定义，使用无INDEX BY的表类型：

CREATE OR REPLACE PACKAGE PKG_PRODUCTS AS
  -- 定义SQL兼容的嵌套表类型（无INDEX BY）
  TYPE t_filter IS TABLE OF VARCHAR2(250);

  PROCEDURE SET_FILTER(p_vendor IN t_filter, p_item IN t_filter);
END PKG_PRODUCTS;
/

2. 修改包体逻辑（无需额外转换，直接使用TABLE()函数）：

CREATE OR REPLACE PACKAGE BODY PKG_PRODUCTS AS
  PROCEDURE SET_FILTER(p_vendor IN t_filter, p_item IN t_filter)
  IS
  v_text VARCHAR2(4000);
  
  BEGIN
    SELECT LISTAGG(Column_Value, ', ' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT) WITHIN GROUP (ORDER BY 1) INTO v_text FROM TABLE(p_vendor);
    INSERT INTO PRODUCT_LOG (LOG_DATE, LOG_TEXT) VALUES (SYSDATE, 'p_vendor:'||v_text);

    SELECT LISTAGG(Column_Value, ', ' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT) WITHIN GROUP (ORDER BY 1) INTO v_text FROM TABLE(p_item);
    INSERT INTO PRODUCT_LOG (LOG_DATE, LOG_TEXT) VALUES (SYSDATE, 'p_item:'||v_text);

    INSERT INTO PRODUCT_FILTER (PRODUCT_ID)
      SELECT PRODUCT_ID FROM PRODUCTS
        WHERE
            (p_vendor IS EMPTY OR VENDOR IN (SELECT * FROM TABLE(p_vendor)))
          AND
            (p_item IS EMPTY OR ITEM IN (SELECT * FROM TABLE(p_item)));
  END SET_FILTER;
END PKG_PRODUCTS;
/

3. 修改调用脚本，使用嵌套表的构造/EXTEND方法添加元素：

declare
  P_VENDOR PKG_PRODUCTS.t_filter := PKG_PRODUCTS.t_filter();
  P_ITEM PKG_PRODUCTS.t_filter := PKG_PRODUCTS.t_filter();

begin
  P_VENDOR.EXTEND;
  P_VENDOR(1) := 'Vendor1';
  P_ITEM.EXTEND;
  P_ITEM(1) := 'Item1';

  PKG_PRODUCTS.SET_FILTER(
      P_VENDOR,
      P_ITEM
  );
end;
/

方案2：保留关联数组，内部转换为嵌套表 #

如果需要保留关联数组（比如兼容原有Web服务调用逻辑），可以在存储过程内部将关联数组转换为SQL可识别的嵌套表类型：

1. 先创建SQL级别的嵌套表类型（包外定义）：

CREATE OR REPLACE TYPE t_filter_sql AS TABLE OF VARCHAR2(250);
/

2. 修改包体，添加关联数组到嵌套表的转换逻辑：

CREATE OR REPLACE PACKAGE BODY PKG_PRODUCTS AS
  PROCEDURE SET_FILTER(p_vendor IN t_filter, p_item IN t_filter)
  IS
  v_text VARCHAR2(4000);
  v_vendor_filter t_filter_sql := t_filter_sql();
  v_item_filter t_filter_sql := t_filter_sql();
  v_idx BINARY_INTEGER;
  
  BEGIN
    -- 转换供应商关联数组到嵌套表
    v_idx := p_vendor.FIRST;
    WHILE v_idx IS NOT NULL LOOP
      v_vendor_filter.EXTEND;
      v_vendor_filter(v_vendor_filter.COUNT) := p_vendor(v_idx);
      v_idx := p_vendor.NEXT(v_idx);
    END LOOP;

    -- 转换商品关联数组到嵌套表
    v_idx := p_item.FIRST;
    WHILE v_idx IS NOT NULL LOOP
      v_item_filter.EXTEND;
      v_item_filter(v_item_filter.COUNT) := p_item(v_idx);
      v_idx := p_item.NEXT(v_idx);
    END LOOP;

    SELECT LISTAGG(Column_Value, ', ' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT) WITHIN GROUP (ORDER BY 1) INTO v_text FROM TABLE(v_vendor_filter);
    INSERT INTO PRODUCT_LOG (LOG_DATE, LOG_TEXT) VALUES (SYSDATE, 'p_vendor:'||v_text);

    SELECT LISTAGG(Column_Value, ', ' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT) WITHIN GROUP (ORDER BY 1) INTO v_text FROM TABLE(v_item_filter);
    INSERT INTO PRODUCT_LOG (LOG_DATE, LOG_TEXT) VALUES (SYSDATE, 'p_item:'||v_text);

    INSERT INTO PRODUCT_FILTER (PRODUCT_ID)
      SELECT PRODUCT_ID FROM PRODUCTS
        WHERE
            (p_vendor.COUNT = 0 OR VENDOR IN (SELECT * FROM TABLE(v_vendor_filter)))
          AND
            (p_item.COUNT = 0 OR ITEM IN (SELECT * FROM TABLE(v_item_filter)));
  END SET_FILTER;
END PKG_PRODUCTS;
/

额外优化提示 #

原逻辑中p_vendor(1) IS NULL的判断存在隐患：如果关联数组的起始索引不是1，或者数组包含多个元素但第一个元素为NULL，会导致过滤逻辑错误。建议替换为判断数组的COUNT是否为0（方案2）或嵌套表是否IS EMPTY（方案1），更准确地处理空过滤条件的场景。

内容的提问来源于stack exchange，提问作者Mark Rhoades