使用EXECUTE IMMEDIATE调用包内存储过程报ORA-00904错误解决方案咨询

开发PLSQL包时需要调用另一个PLSQL包的存储过程，该过程返回3个独立OUT变量：1个SYS_REFCURSOR、1个NUMBER、1个VARCHAR2，调用时出现报错。

报错代码 #

V_QUERY := 'SELECT APPLICATION_POS.PCK_POS_UNO.SP_POS_UNO(:V_NID_DEV, :V_OUT_CUR_RET_BENF, :V_OUT_IDERROR, :V_OUT_DSERROR) FROM DUAL';
EXECUTE IMMEDIATE V_QUERY INTO V_OUT_CUR_RET_BENF, V_OUT_IDERROR, V_OUT_DSERROR USING V_NID_DEV, V_OUT_CUR_RET_BENF, V_OUT_IDERROR, V_OUT_DSERROR ;

错误详情 #

Error: ORA-00904: "APPLICATION_POS"."PCK_POS_UNO"."SP_POS_UNO": invalid identifier -
StackTrace: ORA-06512: in line 24

已尝试的排查方案 #

• 确认可以正常查询APPLICATION_POS用户下的表，仅无法直接列出PCK_POS_UNO包
• 尝试在动态SQL末尾加;，语法错误无效果
• 尝试移除所有者前缀APPLICATION_POS，仍报错
• 尝试直接调用过程加冒号，提示绑定变量未声明
• 尝试直接调用过程不加冒号，报ORA-01001无效游标错误

核心错误根因 #

1. 存储过程不能放在SELECT子句中调用：只有带返回值的FUNCTION支持SELECT调用，存储过程无返回值，这种写法本身语法不合法，是触发ORA-00904的首要原因
2. 跨用户包权限问题：表查询权限和包执行权限是独立的，你当前的APPLICATION用户缺少APPLICATION_POS用户下PCK_POS_UNO包的执行权限
3. 方案4的无效游标错误：存储过程只有参数合法且匹配到数据时才会打开游标，异常场景下游标未初始化，直接操作就会触发报错

修复步骤 #

步骤1：授予包执行权限 #

使用APPLICATION_POS用户登录数据库，执行如下授权语句：

GRANT EXECUTE ON PCK_POS_UNO TO APPLICATION;

步骤2：替换动态SQL为静态调用（推荐） #

删除原有的动态SQL调用代码，直接静态调用存储过程即可，修改后的包体代码片段如下：

create or replace PACKAGE BODY PKG_BH_ONLINE_INFORMATION IS 
PROCEDURE ONLINENOVELTYBEN
(
  V_NID_DEV IN NUMBER,
    CV_1 IN OUT SYS_REFCURSOR
) IS
    
    V_USER VARCHAR2(10 CHAR) := 'INTERNET';
    -- 接收被调用过程返回值的变量
    V_OUT_CUR_RET_BENF SYS_REFCURSOR;
    V_OUT_IDERROR NUMBER;
    V_OUT_DSERROR VARCHAR2(10000 CHAR);
BEGIN

    -- 直接调用跨用户存储过程
    APPLICATION_POS.PCK_POS_UNO.SP_POS_UNO(
        ID_RECORD => V_NID_DEV,
        CUR_RET_BENF => V_OUT_CUR_RET_BENF,
        IDERROR => V_OUT_IDERROR,
        DSERROR => V_OUT_DSERROR
    );

    -- 先判断调用状态再处理游标，避免无效游标错误
    IF V_OUT_IDERROR = -1 THEN
        -- 自定义错误处理逻辑，比如写入调试表
        INSERT INTO tbl_debug(msg_text, record_date)
        VALUES('调用失败：'||V_OUT_DSERROR, SYSDATE);
        COMMIT;
        RETURN;
    ELSE
        -- 正常处理游标逻辑，示例如下
        DECLARE
            v_id_serial NUMBER(9);
            v_serial_nmb NUMBER(12);
        BEGIN
            LOOP
                FETCH V_OUT_CUR_RET_BENF INTO v_id_serial, v_serial_nmb;
                EXIT WHEN V_OUT_CUR_RET_BENF%NOTFOUND;
                -- 此处写业务逻辑
            END LOOP;
            CLOSE V_OUT_CUR_RET_BENF;
            -- 如果需要返回游标给调用方，直接赋值即可：CV_1 := V_OUT_CUR_RET_BENF;
        END;
    END IF;

END ONLINENOVELTYBEN;
END PKG_BH_ONLINE_INFORMATION;
/

可选优化：创建同义词 #

如果不想每次调用都加APPLICATION_POS前缀，可以用APPLICATION用户登录创建同义词：

CREATE SYNONYM PCK_POS_UNO FOR APPLICATION_POS.PCK_POS_UNO;

之后直接调用PCK_POS_UNO.SP_POS_UNO(...)即可。

被调用存储过程优化建议（可选） #

原存储过程正常执行路径下未给IDERROR赋值，默认是NULL，状态判断不清晰，可以在打开游标后增加状态赋值：

OPEN CUR_RET_BENF FOR
select r.id_serial, r.serial_nmb
from tpos_retbenf r 
 where r.id_serial = ID_RECORD;
-- 新增正常状态赋值
IDERROR := 0;
DSERROR := NULL;

内容的提问来源于stack exchange，提问作者Marco Aurelio Fernandez Reyes