ORDS调用含Record与Table Type参数的PL/SQL存储过程求助

关于ORDS对Table Type的支持 #

那个说法不完全准确：ORDS并非只支持VARRAY，它可以支持Table Type，但有严格限制——必须是数据库Schema级别的集合类型，而非PL/SQL包内部定义的私有类型。ORDS无法识别包内的自定义Record和Table Type，这是你当前报错的核心原因。

解决方案：改用Schema级对象/集合类型 #

1. 创建Schema级的对象类型 #

先在数据库中定义对应表头和行项的对象类型，以及行项的Table Type：

-- 定义表头对象类型
CREATE OR REPLACE TYPE xx_header_obj AS OBJECT (
    cust_account_id VARCHAR2(50),
    order_type VARCHAR2(10),
    customer_po VARCHAR2(50),
    sales_person VARCHAR2(50),
    currency_code VARCHAR2(3),
    end_user_address VARCHAR2(200),
    request_date DATE
);
/

-- 定义行项对象类型
CREATE OR REPLACE TYPE xx_line_obj AS OBJECT (
    inv_item_id VARCHAR2(50),
    item_name VARCHAR2(100),
    quantity NUMBER,
    uom VARCHAR2(10),
    plant_number VARCHAR2(20),
    request_date DATE
);
/

-- 定义行项的Table Type
CREATE OR REPLACE TYPE xx_lines_tab AS TABLE OF xx_line_obj;
/

2. 修改存储过程参数类型 #

将原存储过程的p_header_rec和p_sf_lines_tab参数，替换为上面创建的Schema级类型：

CREATE OR REPLACE PACKAGE xx.xx_manage_item IS
    PROCEDURE create_item(
        p_req_id        IN NUMBER,
        p_header_rec    IN xx_header_obj, -- 替换为Schema级对象类型
        p_sf_lines_tab  IN xx_lines_tab,  -- 替换为Schema级Table Type
        x_return_code   OUT NUMBER,
        x_return_msg    OUT VARCHAR2
    );
END xx_manage_item;
/

3. 调整ORDS Handler代码 #

修改Handler的PL/SQL块，确保参数映射正确，同时注意日期类型的转换（因为JSON传入的是字符串，需要转成DATE）：

DECLARE
    x_return_code NUMBER;
    x_return_msg VARCHAR2(200);
    -- 转换JSON日期为DATE类型
    l_header_rec xx_header_obj := xx_header_obj(
        :header_rec.cust_account_id,
        :header_rec.order_type,
        :header_rec.customer_po,
        :header_rec.sales_person,
        :header_rec.currency_code,
        :header_rec.end_user_address,
        TO_DATE(:header_rec.request_date, 'MM/DD/YYYY')
    );
    l_lines_tab xx_lines_tab := xx_lines_tab();
BEGIN
    -- 遍历传入的行项数组，转换并添加到Table Type中
    FOR i IN 1..JSON_ARRAY_LENGTH(:lines_tab) LOOP
        l_lines_tab.EXTEND;
        l_lines_tab(i) := xx_line_obj(
            JSON_VALUE(:lines_tab, '$['||(i-1)||'].inv_item_id'),
            JSON_VALUE(:lines_tab, '$['||(i-1)||'].item_name'),
            TO_NUMBER(JSON_VALUE(:lines_tab, '$['||(i-1)||'].quantity')),
            JSON_VALUE(:lines_tab, '$['||(i-1)||'].uom'),
            JSON_VALUE(:lines_tab, '$['||(i-1)||'].plant_number'),
            TO_DATE(JSON_VALUE(:lines_tab, '$['||(i-1)||'].request_date'), 'MM/DD/YYYY')
        );
    END LOOP;

    xx.xx_manage_item.create_item(
        p_req_id        => :req_id,
        p_header_rec    => l_header_rec,
        p_sf_lines_tab  => l_lines_tab,
        x_return_code   => x_return_code,
        x_return_msg    => x_return_msg
    );

    -- 返回结果给ORDS
    :x_return_code := x_return_code;
    :x_return_msg := x_return_msg;
END;

4. 配置ORDS参数映射 #

在ORDS的Handler配置中，需要将JSON参数映射到PL/SQL变量：

• req_id：映射为IN类型，NUMBER
• header_rec：映射为IN类型，JSON
• lines_tab：映射为IN类型，JSON
• x_return_code：映射为OUT类型，NUMBER
• x_return_msg：映射为OUT类型，VARCHAR2

替代方案：直接传入JSON参数 #

如果不想修改原存储过程的参数类型，可以将整个请求作为一个JSON参数传入，在存储过程内部解析JSON为Record和Table Type：

1. 修改存储过程参数 #

CREATE OR REPLACE PACKAGE xx.xx_manage_item IS
    PROCEDURE create_item(
        p_req_id        IN NUMBER,
        p_request_json  IN CLOB, -- 传入整个JSON请求
        x_return_code   OUT NUMBER,
        x_return_msg    OUT VARCHAR2
    );
END xx_manage_item;
/

2. 存储过程内部解析JSON #

在存储过程体中，使用JSON_VALUE、JSON_TABLE等函数解析JSON，生成需要的Record和Table Type数据，再执行原有逻辑。

3. ORDS Handler简化 #

Handler代码可以简化为：

DECLARE
    x_return_code NUMBER;
    x_return_msg VARCHAR2(200);
BEGIN
    xx.xx_manage_item.create_item(
        p_req_id        => :req_id,
        p_request_json  => :body, -- 直接取整个请求体JSON
        x_return_code   => x_return_code,
        x_return_msg    => x_return_msg
    );
    :x_return_code := x_return_code;
    :x_return_msg := x_return_msg;
END;

注意事项 #

• 日期格式：确保JSON中的日期字符串格式与TO_DATE函数中的格式匹配，避免转换错误。
• 类型匹配：JSON中的数值类型（如quantity）需要转换为对应的PL/SQL类型（如NUMBER），避免隐式转换错误。
• Schema权限：确保ORDS使用的数据库用户有访问这些Schema级对象类型的权限。

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