ORA-40573错误求助：PL/SQL JSON_OBJECT_T使用异常排查

错误原因分析 #

ORA-40573错误的核心是PL/SQL中JSON对象类型的使用违反了Oracle的规范。针对你的代码，问题出在处理poLines数组元素的类型转换上：原代码使用TREAT操作将JSON_ELEMENT_T强制转换为JSON_OBJECT_T，这种方式不符合Oracle PL/SQL JSON类型的转换规则，导致触发该错误。

解决方案 #

1. 修正JSON对象类型转换方式 #

将循环中初始化l_line_obj的代码，从使用TREAT改为直接调用JSON_OBJECT_T构造函数：

原代码：

DECLARE
    l_line_obj JSON_OBJECT_T := TREAT(p_request.get_array('poLines').get(i) AS JSON_OBJECT_T);

修改为：

DECLARE
    l_line_obj JSON_OBJECT_T := JSON_OBJECT_T(p_request.get_array('poLines').get(i));

2. 添加边界校验（可选但推荐） #

为了避免因poLines字段不存在或非数组类型导致的额外错误，在处理PO Lines前添加校验逻辑：

-- 先校验poLines是否存在且为数组类型
IF p_request.has('poLines') AND p_request.get('poLines').is_array THEN
    -- Processing PO Lines
    FOR i IN 0 .. p_request.get_array('poLines').get_size - 1 LOOP
        -- 内部循环逻辑...
    END LOOP;
END IF;

完整修改后的包体代码 #

CREATE OR REPLACE PACKAGE BODY XX_MESSAGE_PKG1
IS
    PROCEDURE generic_service ( p_request IN json_object_t,
                           p_response OUT json_object_t )
    AS
        l_po_header_id NUMBER;
        l_resp_obj JSON_OBJECT_T := JSON_OBJECT_T();
    BEGIN
        -- Processing Header based on operation
        CASE LOWER(p_request.get_string('operation'))
            WHEN 'add' THEN
                INSERT INTO PO_HEADER (PO_NUMBER, VENDOR_NAME, ORDER_DATE, TOTAL_AMOUNT, STATUS, CREATED_BY, CREATED_DATE)
                VALUES (
                    p_request.get_string('poNumber'),
                    p_request.get_string('vendorName'),
                    TO_DATE(p_request.get_string('orderDate'), 'YYYY-MM-DD'),
                    p_request.get_number('totalAmount'),
                    p_request.get_string('status'),
                    p_request.get_string('createdBy'),
                    SYSDATE
                )
                RETURNING PO_HEADER_ID INTO l_po_header_id;

            WHEN 'update' THEN
                UPDATE PO_HEADER
                SET VENDOR_NAME = p_request.get_string('vendorName'),
                    ORDER_DATE = TO_DATE(p_request.get_string('orderDate'), 'YYYY-MM-DD'),
                    TOTAL_AMOUNT = p_request.get_number('totalAmount'),
                    STATUS = p_request.get_string('status'),
                    UPDATED_BY = p_request.get_string('createdBy'),
                    UPDATED_DATE = SYSDATE
                WHERE PO_NUMBER = p_request.get_string('poNumber')
                RETURNING PO_HEADER_ID INTO l_po_header_id;

            WHEN 'remove' THEN
                DELETE FROM PO_HEADER WHERE PO_NUMBER = p_request.get_string('poNumber');
                l_resp_obj.put('status', 'SUCCESS');
                l_resp_obj.put('message', 'PO_HEADER deleted');
                p_response := l_resp_obj;
                RETURN;

            ELSE
                RAISE_APPLICATION_ERROR(-20001, 'Invalid operation for PO_HEADER');
        END CASE;

        -- Processing PO Lines with validation
        IF p_request.has('poLines') AND p_request.get('poLines').is_array THEN
            FOR i IN 0 .. p_request.get_array('poLines').get_size - 1 LOOP
                DECLARE
                    l_line_obj JSON_OBJECT_T := JSON_OBJECT_T(p_request.get_array('poLines').get(i));
                BEGIN
                    CASE LOWER(l_line_obj.get_string('operation'))
                        WHEN 'add' THEN
                            INSERT INTO PO_LINES (PO_HEADER_ID, LINE_NUMBER, ITEM_CODE, ITEM_DESCRIPTION, 
                                                  QUANTITY, UNIT_PRICE, CREATED_BY, CREATED_DATE)
                            VALUES (
                                l_po_header_id,
                                l_line_obj.get_number('lineNumber'),
                                l_line_obj.get_string('itemCode'),
                                l_line_obj.get_string('itemDescription'),
                                l_line_obj.get_number('quantity'),
                                l_line_obj.get_number('unitPrice'),
                                l_line_obj.get_string('createdBy'),
                                SYSDATE
                            );

                        WHEN 'update' THEN
                            UPDATE PO_LINES
                            SET ITEM_CODE = l_line_obj.get_string('itemCode'),
                                ITEM_DESCRIPTION = l_line_obj.get_string('itemDescription'),
                                QUANTITY = l_line_obj.get_number('quantity'),
                                UNIT_PRICE = l_line_obj.get_number('unitPrice'),
                                UPDATED_BY = l_line_obj.get_string('createdBy'),
                                UPDATED_DATE = SYSDATE
                            WHERE PO_HEADER_ID = l_po_header_id
                              AND LINE_NUMBER = l_line_obj.get_number('lineNumber');

                        WHEN 'remove' THEN
                            DELETE FROM PO_LINES
                            WHERE PO_HEADER_ID = l_po_header_id
                              AND LINE_NUMBER = l_line_obj.get_number('lineNumber');

                        ELSE
                            RAISE_APPLICATION_ERROR(-20002, 'Invalid operation for PO_LINES');
                    END CASE;
                END;
            END LOOP;
        END IF;

        -- Success response
        l_resp_obj.put('status', 'SUCCESS');
        l_resp_obj.put('po_header_id', l_po_header_id);
        p_response := l_resp_obj;

    EXCEPTION
        WHEN OTHERS THEN
            l_resp_obj.put('status', 'ERROR');
            l_resp_obj.put('description', 'Error Code: ' || SQLCODE || ' - ' || SQLERRM);
            p_response := l_resp_obj;
    END generic_service;
END XX_MESSAGE_PKG1;

额外注意事项 #

• 确保你的Oracle数据库版本为12cR2（12.2）及以上，因为JSON_OBJECT_T等PL/SQL JSON类型是从该版本开始引入的。
• 传入的JSON中，poLines数组内的元素必须是标准JSON对象，否则仍可能触发类型转换错误。

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