如何正确定义接收关联数组参数的Oracle ORDS GET Handler

问题根源 #

PLS-00306错误的核心原因是：ORDS无法直接将GET请求的查询参数自动映射到dbms_sql.varchar2_table类型的关联数组。GET请求的参数本质都是字符串类型，必须手动在Handler的PL/SQL逻辑中完成类型转换。

前提：确认存储过程结构 #

假设你的test_array存储过程定义如下（如果结构不同，只需调整转换逻辑即可）：

CREATE OR REPLACE PROCEDURE test_array(
    p_input IN dbms_sql.varchar2_table,
    p_output OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_output FOR
        SELECT column_value AS item FROM TABLE(p_input);
END test_array;
/

正确的Handler定义方式 #

方式1：通过PL/SQL API创建 #

BEGIN
    -- 定义Handler
    ORDS.DEFINE_HANDLER(
        p_module_name    => 'test_array_module',
        p_pattern        => 'call-proc',
        p_method         => 'GET',
        p_source_type    => ORDS.source_type_plsql,
        p_source         => q'[
            DECLARE
                l_input  dbms_sql.varchar2_table;
                l_output SYS_REFCURSOR;
            BEGIN
                -- 将多值查询参数转换为dbms_sql.varchar2_table
                l_input := dbms_sql.varchar2_table();
                FOR i IN 1 TO :input.COUNT LOOP
                    l_input.EXTEND;
                    l_input(i) := :input(i);
                END LOOP;
                
                -- 调用目标存储过程
                test_array(p_input => l_input, p_output => l_output);
                
                -- 返回游标结果
                ORDS.DEFINE_RESPONSE(p_result => l_output);
            END;
        ]',
        p_items_per_page => 0
    );

    -- 定义请求参数：允许同名多值，类型为STRING
    ORDS.DEFINE_PARAMETER(
        p_module_name        => 'test_array_module',
        p_pattern            => 'call-proc',
        p_method             => 'GET',
        p_name               => 'input',
        p_bind_variable_name => 'input',
        p_source_type        => ORDS.source_type_query,
        p_param_type         => ORDS.param_type_string,
        p_access_method      => 'IN',
        p_allow_multiple     => TRUE -- 关键：开启多值参数支持
    );

    COMMIT;
END;
/

方式2：通过ORDS Web界面配置 #

如果使用Oracle APEX或ORDS管理界面：

• 新建GET类型的Handler，设置路径（如/test/call-proc）
• 在PL/SQL代码框中写入上述类型转换与存储过程调用逻辑
• 添加请求参数input，类型选择STRING，勾选允许多个值选项

调用示例 #

通过GET请求传递多个同名参数，ORDS会自动将其收集为字符串数组：

GET https://your-ords-domain/test/call-proc?input=apple&input=banana&input=cherry

核心注意事项 #

• 关联数组参数不能直接与ORDS的GET参数绑定，必须通过STRING类型的多值参数接收后手动转换
• 必须开启多值参数支持（p_allow_multiple => TRUE），否则ORDS只会保留最后一个同名参数
• 若需求是通过逗号分隔的单参数传递数组，也可将单个STRING参数拆分后转换为关联数组，但多参数方式更符合REST规范

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