从Shell脚本向PL/SQL过程传递数组遇参数类型错误的问题

你遇到的ORA-06550和PLS-00306错误，核心原因是参数类型不匹配：

• 存储过程process_failed_requests的p_nations参数定义为VARCHAR2，接受逗号分隔的字符串
• 但Shell脚本的PL/SQL块中，你定义了局部自定义表类型nation_list，并将$nation转成数组后传递，两者类型完全不兼容

以下是两种可行的解决方案：

方案一：直接传递逗号分隔字符串（无需修改存储过程） #

这是最简单的解决方式，因为你的存储过程原本就是设计用来处理逗号分隔字符串的，不需要额外转成数组。

修改后的Shell脚本代码：

sqlplus -s $RAN_UID <<EOF > $OUTPUTS/$file.txt
set serveroutput on;
begin
    dbms_output.put_line('Nation list = ' || '$nation');
    process_failed_requests(p_nations => '$nation', p_lastrundate => '$LastRun');
end;
/
EOF

注意：如果$nation变量中包含单引号，需要先在Shell中做转义处理（比如${nation//\'/''}），避免SQL语法错误。

方案二：修改存储过程接受PL/SQL表类型（适合复杂场景） #

如果需要用数组方式传递参数（比如后续要扩展更复杂的参数逻辑），需要调整存储过程和类型定义：

步骤1：创建全局表类型 #

首先在数据库中创建全局可访问的表类型（匿名块中的局部类型无法被存储过程识别）：

CREATE OR REPLACE TYPE nation_list AS TABLE OF VARCHAR2(100);
/

步骤2：修改存储过程参数类型和查询逻辑 #

将存储过程的p_nations参数改为nation_list类型，并调整内部查询直接使用表类型的成员：

CREATE OR REPLACE PROCEDURE process_failed_requests(p_nations IN nation_list, p_lastrundate IN VARCHAR2) AS
BEGIN
    FOR rec IN (
        SELECT B.CIDGEN, B.NATION
        FROM COMPANY B
        JOIN NATION_LOOKUP C ON B.NATION = C.CODE
        WHERE B.CIDGEN IN (
            SELECT CIDGEN
            FROM RAN.OA_REQUEST_RESPONSE_STATUS
            WHERE RESPONSE_STATUS = 'FailedRequest'
            AND RESPONSE_UPDATE_STAMP >= TO_DATE(p_lastrundate, 'DD-MON-YYYY HH24:MI:SS')
        )
        AND B.NATION IN (SELECT column_value FROM TABLE(p_nations))
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.CIDGEN || '||' || rec.NATION);
    END LOOP;
END;
/

步骤3：保留原Shell脚本的数组传递逻辑 #

此时Shell脚本中的PL/SQL块可以继续使用数组定义，因为存储过程已经兼容该类型：

sqlplus -s $RAN_UID <<EOF > $OUTPUTS/$file.txt
set serveroutput on;
declare
    v_nations nation_list := nation_list(${nation//,/','});
begin
    for i in 1..v_nations.count loop
        dbms_output.put_line('Nation = ' || v_nations(i));
    end loop;
    process_failed_requests(p_nations => v_nations, p_lastrundate => '$LastRun');
end;
/
EOF

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