时间戳

首页 > 解决方案 > PLS-00341：游标“PROJECT_PARAMS_CSR”的声明不完整或格式错误

database - PLS-00341：游标“PROJECT_PARAMS_CSR”的声明不完整或格式错误

问题描述

这在 oracle 12.2 中完美运行。问题出现在 11g R2 中。

我在光标下方。

CURSOR project_params_csr
    IS
      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 (SELECT * FROM TABLE(g_project_param_org_ids_tbl))
        AND pp.name LIKE p_prefix || '%';

在下面的这个光标中是声明部分中的变量。

p_prefix          IN         VARCHAR2,

g_project_param_org_ids_tbl是在包规范中定义并在正文中初始化的表。

TYPE number_tbl_type IS TABLE OF NUMBER;    
  g_project_param_org_ids_tbl  number_tbl_type;
  g_project_param_org_ids_tbl := number_tbl_type(602, 603);

g_project_param_cst_group_id也是全局变量，在包规范中定义。

  g_project_param_cst_group_id NUMBER := 1020;

可能是什么问题？我该如何修改它？

标签： databaseoracleplsqloracle11goracle12c

解决方案

问题出在下面的一个

AND pop.organization_id IN (SELECT * FROM TABLE(g_project_param_org_ids_tbl))

在 Oracle 12c 之前，TABLE() 函数只能应用于模式中定义的集合。（作为 SQL 类型）

所以，Oracle 11g 不理解g_project_param_org_ids_tbl这种情况。您可能应该首先将其创建为 SQL 类型

CREATE OR REPLACE TYPE number_tbl_type IS TABLE OF NUMBER; 
/

推荐阅读