You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

Oracle 19c ATP中ORDS REST API的PL/SQL JSON解析错误修复求助

avatar
安全阿岳
2026-6-14
Oracle 19c ATP ORDS REST API PL/SQL块修复方案

问题概述

在Oracle 19c ATP数据库中开发ORDS REST API，通过POST请求接收JSON负载实现PO_HEADER和PO_LINES表的增删改操作，但PL/SQL块运行报错：ORA-06550、PLS-00201（标识符'JSON'未声明）。

原表结构 

-- Creating the PO_HEADER table
CREATE TABLE PO_HEADER (
    PO_HEADER_ID     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 
    PO_NUMBER        VARCHAR2(50) UNIQUE, 
    VENDOR_NAME      VARCHAR2(100),
    ORDER_DATE       DATE DEFAULT SYSDATE,
    TOTAL_AMOUNT     NUMBER(15,2) DEFAULT 0,
    STATUS           VARCHAR2(20) DEFAULT 'DRAFT',
    CREATED_BY       VARCHAR2(50),
    CREATED_DATE     DATE DEFAULT SYSDATE,
    UPDATED_BY       VARCHAR2(50),
    UPDATED_DATE     DATE
);

-- Creating the PO_LINES table without NOT NULL constraints
CREATE TABLE PO_LINES (
    PO_LINE_ID       NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 
    PO_HEADER_ID     NUMBER,  -- No FK constraint
    LINE_NUMBER      NUMBER,  -- Sequential line number
    ITEM_CODE        VARCHAR2(50),
    ITEM_DESCRIPTION VARCHAR2(200),
    QUANTITY         NUMBER(10,2) DEFAULT 1 CHECK (QUANTITY > 0),
    UNIT_PRICE       NUMBER(10,2) DEFAULT 0 CHECK (UNIT_PRICE >= 0),
    LINE_TOTAL       NUMBER(15,2) GENERATED ALWAYS AS (QUANTITY * UNIT_PRICE) VIRTUAL, 
    CREATED_BY       VARCHAR2(50),
    CREATED_DATE     DATE DEFAULT SYSDATE,
    UPDATED_BY       VARCHAR2(50),
    UPDATED_DATE     DATE
);

-- Creating indexes for performance
CREATE INDEX IDX_PO_HEADER_DATE ON PO_HEADER (ORDER_DATE);
CREATE INDEX IDX_PO_LINES_HEADER_ID ON PO_LINES (PO_HEADER_ID);

原PL/SQL代码（报错版本） 

DECLARE
    v_json  JSON := JSON(:body);  -- ORDS passes JSON as CLOB, converted to JSON type
    v_poNumber VARCHAR2(50);
    v_operation VARCHAR2(10);
    v_header_id NUMBER;
BEGIN
    -- Extract header-level values
    v_poNumber := v_json.poNumber;
    v_operation := v_json.operation;

    -- Handle Header Operations
    IF v_operation = 'add' THEN
        INSERT INTO PO_HEADER (PO_NUMBER, VENDOR_NAME, ORDER_DATE, TOTAL_AMOUNT, STATUS, CREATED_BY)
        VALUES (
            v_json.poNumber,
            v_json.vendorName,
            TO_DATE(v_json.orderDate, 'YYYY-MM-DD'),
            v_json.totalAmount,
            v_json.status,
            v_json.createdBy
        )
        RETURNING PO_HEADER_ID INTO v_header_id;

    ELSIF v_operation = 'update' THEN
        UPDATE PO_HEADER
        SET VENDOR_NAME = v_json.vendorName,
            ORDER_DATE = TO_DATE(v_json.orderDate, 'YYYY-MM-DD'),
            TOTAL_AMOUNT = v_json.totalAmount,
            STATUS = v_json.status,
            UPDATED_BY = v_json.createdBy,
            UPDATED_DATE = SYSDATE
        WHERE PO_NUMBER = v_poNumber
        RETURNING PO_HEADER_ID INTO v_header_id;

    ELSIF v_operation = 'remove' THEN
        DELETE FROM PO_HEADER WHERE PO_NUMBER = v_poNumber;
        DELETE FROM PO_LINES WHERE PO_HEADER_ID = (SELECT PO_HEADER_ID FROM PO_HEADER WHERE PO_NUMBER = v_poNumber);
        RETURN;
    END IF;

    -- Process PO Lines Using JSON_TABLE with JSON Type
    FOR line IN (
        SELECT *
        FROM JSON_TABLE(v_json.poLines, '$[*]'
            COLUMNS (
                operation VARCHAR2(10) PATH '$.operation',
                lineNumber NUMBER PATH '$.lineNumber',
                itemCode VARCHAR2(50) PATH '$.itemCode',
                itemDescription VARCHAR2(200) PATH '$.itemDescription',
                quantity NUMBER PATH '$.quantity',
                unitPrice NUMBER PATH '$.unitPrice',
                createdBy VARCHAR2(50) PATH '$.createdBy'
            )
        )
    ) LOOP
        IF line.operation = 'Add' THEN
            INSERT INTO PO_LINES (PO_HEADER_ID, LINE_NUMBER, ITEM_CODE, ITEM_DESCRIPTION, QUANTITY, UNIT_PRICE, CREATED_BY)
            VALUES (v_header_id, line.lineNumber, line.itemCode, line.itemDescription, line.quantity, line.unitPrice, line.createdBy);

        ELSIF line.operation = 'Update' THEN
            UPDATE PO_LINES
            SET ITEM_CODE = line.itemCode,
                ITEM_DESCRIPTION = line.itemDescription,
                QUANTITY = line.quantity,
                UNIT_PRICE = line.unitPrice,
                UPDATED_BY = line.createdBy,
                UPDATED_DATE = SYSDATE
            WHERE PO_HEADER_ID = v_header_id AND LINE_NUMBER = line.lineNumber;

        ELSIF line.operation = 'Remove' THEN
            DELETE FROM PO_LINES WHERE PO_HEADER_ID = v_header_id AND LINE_NUMBER = line.lineNumber;
        END IF;
    END LOOP;

    COMMIT;
END;

原JSON输入负载（含语法错误） 

{
    "poNumber": "PO1004",
    "vendorName": "Tech Solutions",
    "orderDate": "2025-03-01",
    "totalAmount": 800.75,
    "status": "APPROVED",
    "createdBy": "User3",
    "operation": "add",
    "poLines": [
        {
            "operation": "Add",
            "lineNumber": 1,
            "itemCode": "ITEM005",
            "itemDescription": "Keyboard",
            "quantity": 4,
            "unitPrice": 50.00,
            "createdBy": "User3"
        },
        {
            "operation": "Remove",
            "lineNumber": 2,
            "itemCode": "ITEM006",
            "itemDescription": "Mouse Pad",
            "quantity": 10,
            "unitPrice": 8.50,
            "createdBy": "User3"
        },
        ,
        {
            "operation": "update",
            "lineNumber": 2,
            "itemCode": "ITEM006",
            "itemDescription": "Mouse Pad",
            "quantity": 10,
            "unitPrice": 8.50,
            "createdBy": "User3"
        }
    ]
}

修复要点及修正后的代码

核心错误原因

  1. Oracle 19c中没有直接可用的JSON类型用于实例化，需使用Oracle官方提供的SYS.JSON_OBJECT_T类型。
  2. 原代码直接用.访问JSON属性的方式不被支持，需通过JSON_OBJECT_T的方法提取值。
  3. 删除头表记录后无法再查询到PO_HEADER_ID，需调整删除逻辑顺序。
  4. 原JSON负载存在语法错误（数组中多余的逗号）。

修正后的PL/SQL代码 

DECLARE
    v_json_obj SYS.JSON_OBJECT_T := SYS.JSON_OBJECT_T(:body); -- 使用Oracle原生JSON对象类型
    v_poNumber VARCHAR2(50);
    v_operation VARCHAR2(10);
    v_header_id NUMBER;
    v_po_lines SYS.JSON_ARRAY_T;
    v_line_obj SYS.JSON_OBJECT_T;
BEGIN
    -- 提取头层级属性
    v_poNumber := v_json_obj.get_String('poNumber');
    v_operation := v_json_obj.get_String('operation');

    -- 处理头表操作
    IF v_operation = 'add' THEN
        INSERT INTO PO_HEADER (PO_NUMBER, VENDOR_NAME, ORDER_DATE, TOTAL_AMOUNT, STATUS, CREATED_BY)
        VALUES (
            v_poNumber,
            v_json_obj.get_String('vendorName'),
            TO_DATE(v_json_obj.get_String('orderDate'), 'YYYY-MM-DD'),
            v_json_obj.get_Number('totalAmount'),
            v_json_obj.get_String('status'),
            v_json_obj.get_String('createdBy')
        )
        RETURNING PO_HEADER_ID INTO v_header_id;

    ELSIF v_operation = 'update' THEN
        UPDATE PO_HEADER
        SET VENDOR_NAME = v_json_obj.get_String('vendorName'),
            ORDER_DATE = TO_DATE(v_json_obj.get_String('orderDate'), 'YYYY-MM-DD'),
            TOTAL_AMOUNT = v_json_obj.get_Number('totalAmount'),
            STATUS = v_json_obj.get_String('status'),
            UPDATED_BY = v_json_obj.get_String('createdBy'),
            UPDATED_DATE = SYSDATE
        WHERE PO_NUMBER = v_poNumber
        RETURNING PO_HEADER_ID INTO v_header_id;

    ELSIF v_operation = 'remove' THEN
        -- 先获取头ID再删除子表，避免删除后无法查询
        SELECT PO_HEADER_ID INTO v_header_id FROM PO_HEADER WHERE PO_NUMBER = v_poNumber;
        DELETE FROM PO_LINES WHERE PO_HEADER_ID = v_header_id;
        DELETE FROM PO_HEADER WHERE PO_NUMBER = v_poNumber;
        COMMIT;
        RETURN;
    END IF;

    -- 处理行表操作：遍历JSON数组
    v_po_lines := v_json_obj.get_JSONArray('poLines');
    FOR i IN 0..v_po_lines.get_Size()-1 LOOP
        v_line_obj := v_po_lines.get_Object(i);
        CASE UPPER(v_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)
                VALUES (
                    v_header_id,
                    v_line_obj.get_Number('lineNumber'),
                    v_line_obj.get_String('itemCode'),
                    v_line_obj.get_String('itemDescription'),
                    v_line_obj.get_Number('quantity'),
                    v_line_obj.get_Number('unitPrice'),
                    v_line_obj.get_String('createdBy')
                );
            WHEN 'UPDATE' THEN
                UPDATE PO_LINES
                SET ITEM_CODE = v_line_obj.get_String('itemCode'),
                    ITEM_DESCRIPTION = v_line_obj.get_String('itemDescription'),
                    QUANTITY = v_line_obj.get_Number('quantity'),
                    UNIT_PRICE = v_line_obj.get_Number('unitPrice'),
                    UPDATED_BY = v_line_obj.get_String('createdBy'),
                    UPDATED_DATE = SYSDATE
                WHERE PO_HEADER_ID = v_header_id AND LINE_NUMBER = v_line_obj.get_Number('lineNumber');
            WHEN 'REMOVE' THEN
                DELETE FROM PO_LINES 
                WHERE PO_HEADER_ID = v_header_id AND LINE_NUMBER = v_line_obj.get_Number('lineNumber');
        END CASE;
    END LOOP;

    COMMIT;
END;

修正后的JSON输入负载 

{
    "poNumber": "PO1004",
    "vendorName": "Tech Solutions",
    "orderDate": "2025-03-01",
    "totalAmount": 800.75,
    "status": "APPROVED",
    "createdBy": "User3",
    "operation": "add",
    "poLines": [
        {
            "operation": "Add",
            "lineNumber": 1,
            "itemCode": "ITEM005",
            "itemDescription": "Keyboard",
            "quantity": 4,
            "unitPrice": 50.00,
            "createdBy": "User3"
        },
        {
            "operation": "Remove",
            "lineNumber": 2,
            "itemCode": "ITEM006",
            "itemDescription": "Mouse Pad",
            "quantity": 10,
            "unitPrice": 8.50,
            "createdBy": "User3"
        },
        {
            "operation": "update",
            "lineNumber": 2,
            "itemCode": "ITEM006",
            "itemDescription": "Mouse Pad",
            "quantity": 10,
            "unitPrice": 8.50,
            "createdBy": "User3"
        }
    ]
}

验证说明

  1. 确保ORDS已正确配置，POST请求的Content-Type设置为application/json。
  2. 修正后的代码使用Oracle 19c原生支持的JSON API类型，彻底解决未声明标识符的错误。
  3. 调整删除逻辑顺序，先获取头ID再删除子表记录，保证数据一致性。
  4. 使用UPPER()统一操作符的大小写判断，避免大小写不匹配导致的逻辑失效。

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.06.14 05:24:54