PLS-00302错误求助：GEN_BULL_PAY组件未声明问题排查

问题场景 #

用户创建了指定存储过程后，调用时触发PLS-00302错误，提示组件未声明。

存储过程代码 #

CREATE OR REPLACE PROCEDURE SCHEMAUSER.GEN_BULL_PAY(p_deadline IN VARCHAR2)
as
BEGIN
    select BULL_ROW
    from SCHEMAUSER.BULL_CP
    where adress is not null and ACCOUNT_TYPE = 'NATIO'
        and TERM = to_date(p_deadline , 'dd/mm/yyyy')
       or (ACCOUNT_TYPE is null or CODE_PAYMENT_METHOD = 'M')
    order by NUM_RCAR, ITEM_ORDER;
    commit;
end;

调用语句 #

begin
    SCHEMAUSER.GEN_BULL_PAY;
end;

错误信息 #

[2023-07-13 15:45:06] [65000][6550]
[2023-07-13 15:45:06]   ORA-06550: Ligne 2, colonne 17 :
[2023-07-13 15:45:06]   PLS-00302: PLS-00302: component 'GEN_BULL_PAY' must be declared
[2023-07-13 15:45:06]   ORA-06550: Ligne 2, colonne 5 :
[2023-07-13 15:45:06]   PL/SQL: Statement ignored
[2023-07-13 15:45:06] Position: 22

排查原因 #

1. 存储过程编译失败，未实际创建
   PL/SQL规范中，存储过程的BEGIN块内不能直接执行SELECT语句却不接收结果集。原存储过程的SELECT既没有用INTO子句接收单条记录，也没有用游标处理多条记录，会直接导致存储过程编译报错，最终并未成功在数据库中创建该组件。调用不存在的存储过程，自然会触发"组件未声明"的错误。

2. 调用时未传递必填参数
   即使存储过程编译成功，其定义时明确要求传入p_deadline参数，但调用语句未传递任何参数，Oracle会尝试寻找无参的GEN_BULL_PAY存储过程，找不到则报组件未声明。

解决方案 #

步骤1：修复存储过程编译错误 #

根据业务需求处理SELECT的结果，示例如下：

• 若仅需获取单条记录，用INTO接收：

CREATE OR REPLACE PROCEDURE SCHEMAUSER.GEN_BULL_PAY(p_deadline IN VARCHAR2)
as
    v_bull_row SCHEMAUSER.BULL_CP.BULL_ROW%TYPE;
BEGIN
    select BULL_ROW
    into v_bull_row
    from SCHEMAUSER.BULL_CP
    where adress is not null and ACCOUNT_TYPE = 'NATIO'
        and TERM = to_date(p_deadline , 'dd/mm/yyyy')
       or (ACCOUNT_TYPE is null or CODE_PAYMENT_METHOD = 'M')
    order by NUM_RCAR, ITEM_ORDER
    fetch first 1 row only; -- 避免返回多条记录触发报错
    commit;
end;

• 若需处理多条记录，用游标循环：

CREATE OR REPLACE PROCEDURE SCHEMAUSER.GEN_BULL_PAY(p_deadline IN VARCHAR2)
as
    cursor c_bull is
        select BULL_ROW
        from SCHEMAUSER.BULL_CP
        where adress is not null and ACCOUNT_TYPE = 'NATIO'
            and TERM = to_date(p_deadline , 'dd/mm/yyyy')
           or (ACCOUNT_TYPE is null or CODE_PAYMENT_METHOD = 'M')
        order by NUM_RCAR, ITEM_ORDER;
    v_bull_row SCHEMAUSER.BULL_CP.BULL_ROW%TYPE;
BEGIN
    open c_bull;
    loop
        fetch c_bull into v_bull_row;
        exit when c_bull%notfound;
        -- 此处添加业务逻辑处理v_bull_row
    end loop;
    close c_bull;
    commit;
end;

步骤2：正确调用存储过程 #

调用时必须传递符合dd/mm/yyyy格式的日期参数：

begin
    SCHEMAUSER.GEN_BULL_PAY('13/07/2023');
end;

内容的提问来源于stack exchange，提问作者BAKHALED Ibrahim