问题原因 #

这个报错的核心是Oracle版本特性差异：Oracle 12.2及以上支持在SQL语句中直接引用包级自定义集合类型，但11g R2并不支持。

你的游标定义里用到了SELECT * FROM TABLE(g_project_param_org_ids_tbl)，其中g_project_param_org_ids_tbl是包内部定义的number_tbl_type集合。11g的SQL解析引擎无法识别这种仅存在于PL/SQL包内的集合类型，导致游标声明解析失败，抛出PLS-00341: declaration of cursor 'PROJECT_PARAMS_CSR' is incomplete or malformed错误。

解决方案 #

这里提供两种可行的修改方案，你可以根据实际场景选择：

方案一：将集合类型定义为SQL级全局类型（推荐）

把原本在包内定义的集合类型移到SQL层面创建，让11g的SQL引擎能识别它：

1. 先在SQL环境下创建集合类型：

CREATE OR REPLACE TYPE number_tbl_type AS TABLE OF NUMBER;
/

2. 修改包内的变量定义，使用这个全局类型：

-- 包规范部分
g_project_param_org_ids_tbl number_tbl_type;

-- 包体初始化部分
g_project_param_org_ids_tbl := number_tbl_type(602, 603);

修改后，原来的游标定义可以完全保留，不需要改动其他逻辑，11g就能正常解析。

方案二：用动态SQL拼接IN子句（无需创建全局类型）

如果不想在数据库中创建全局类型，可以通过动态SQL把集合元素拼接成IN子句，替代TABLE()函数的用法：

DECLARE
  v_in_clause VARCHAR2(1000); -- 根据集合大小调整长度
  -- 定义与游标返回结构匹配的记录类型
  TYPE project_params_rec IS RECORD(
    project_id NUMBER,
    organization_id NUMBER,
    costing_group_id NUMBER,
    wip_acct_class_code VARCHAR2(100),
    ipv_expenditure_type VARCHAR2(100),
    erv_expenditure_type VARCHAR2(100),
    freight_expenditure_type VARCHAR2(100),
    tax_expenditure_type VARCHAR2(100),
    misc_expenditure_type VARCHAR2(100)
  );
  project_params_csr SYS_REFCURSOR;
  v_rec project_params_rec;
BEGIN
  -- 遍历集合，拼接IN子句的元素
  FOR i IN 1..g_project_param_org_ids_tbl.COUNT LOOP
    v_in_clause := v_in_clause || CASE WHEN i > 1 THEN ',' ELSE '' END || g_project_param_org_ids_tbl(i);
  END LOOP;

  -- 动态打开游标，注意单引号的转义
  OPEN project_params_csr FOR
    'SELECT project_id, pop.organization_id, 
     DECODE(mp.primary_cost_method, 1, NULL, 2, ' || g_project_param_cst_group_id || ') AS costing_group_id, 
     wp.default_discrete_class AS wip_acct_class_code, 
     DECODE(pop.transfer_ipv, ''Y'', pop.ipv_expenditure_type, NULL) AS ipv_expenditure_type, 
     DECODE(pop.transfer_erv, ''Y'', pop.erv_expenditure_type, NULL) AS erv_expenditure_type, 
     DECODE(pop.transfer_freight, ''Y'', pop.freight_expenditure_type, NULL) AS freight_expenditure_type, 
     DECODE(pop.transfer_tax, ''Y'', pop.tax_expenditure_type, NULL) AS tax_expenditure_type, 
     DECODE(pop.transfer_misc, ''Y'', pop.misc_expenditure_type, NULL) AS misc_expenditure_type 
     FROM pa_projects_all pp, pjm_org_parameters pop, mtl_parameters mp, wip_parameters wp 
     WHERE pp.pm_product_code = ''PJM'' 
     AND mp.organization_id = pop.organization_id 
     AND wp.organization_id = pop.organization_id 
     AND pop.organization_id IN (' || v_in_clause || ')
     AND pp.name LIKE ''' || p_prefix || '%''';

  -- 处理游标数据
  LOOP
    FETCH project_params_csr INTO v_rec;
    EXIT WHEN project_params_csr%NOTFOUND;
    -- 这里添加你的业务逻辑处理代码
  END LOOP;
  CLOSE project_params_csr;
END;
/

如果p_prefix是用户输入，建议用绑定变量优化避免SQL注入：

-- 优化后的动态SQL（使用绑定变量）
OPEN project_params_csr FOR
  'SELECT ... 
   AND pp.name LIKE :prefix'
USING p_prefix;

内容的提问来源于stack exchange，提问作者Shruti sharma