如何将Webhook传入的JSON数据插入Oracle APEX的表中

算力阿峰

2026-6-18

问题描述

我有一个Oracle APEX应用，需要接收Webhook传来的JSON数据，并将其中部分字段插入到指定表中。JSON数据结构如下：

{"event_type": "WEBHOOK.MARKED_OPPORTUNITY","entity_type": "CONTACT","event_identifier": "my-event-identifier","timestamp": "2018-03-13T14:09:02.724-03:00","event_timestamp": "2018-03-13T14:07:04.254-03:00","contact": {"uuid": "c2f3d2b3-7250-4d27-97f4-eef38be32f7f","email": "support@example.org","name": "Contact Name","job_title": "Developer","bio": "This is my bio","website": "http://rdstation.com.br","personal_phone": "48 30252598","mobile_phone": "48 30252598","city": "Florianópolis","facebook": "Contact Facebook","linkedin": "Contact Linkedin","twitter": "Contact Twitter","tags": ["tag 1","tag 2"],"cf_custom_field_example": ["Option1","Option2"],"company": {"name": "Company Example 0"},"funnel": {"name": "default","lifecycle_stage": "Lead","opportunity": false,"contact_owner_email": "owner@example.org","interest": 20,"fit": 0,"origin": "Orgânico"}}}

需求是触发POST请求时，将JSON中contact对象下的name和email值插入到LEADS表中。我尝试在RESTful数据服务的POST方法中编写了如下PL/SQL代码，但未能正确实现需求：

DECLARE
    new_id INTEGER;
    current_date DATE;
    blob_body BLOB := :body;
    clob_variable CLOB := CONVERT_TO_CLOB(blob_body);
    
    v_name VARCHAR2(100);
    v_email VARCHAR2(100);
BEGIN
    SELECT SYSDATE INTO current_date FROM dual;

    DECLARE
        v_json_obj JSON_OBJECT_T;
    BEGIN
        v_json_obj := JSON_OBJECT_T.parse(clob_variable);
        
        v_name := v_json_obj.get_String('name');
        v_email := v_json_obj.get_String('email');
    END;

    INSERT INTO LEADS (ID, NOME, EMAIL)
    VALUES (121212, v_name, v_email)
    RETURNING ID INTO new_id;

    :status_code := 201;
    :forward_location := '../employees/' || new_id;

EXCEPTION
    WHEN VALUE_ERROR THEN
        :errmsg := 'Wrong value.';
        :status_code := 400;
    WHEN OTHERS THEN 
        :status_code := 400;
        :errmsg := SQLERRM;
END;

请问该如何修改代码以完成数据插入？

解决方案

你的代码核心问题是未正确定位到contact子对象下的name和email字段，同时存在几个可优化的细节，修改后的代码如下：

DECLARE
    new_id INTEGER;
    blob_body BLOB := :body;
    clob_variable CLOB;
    v_json_obj JSON_OBJECT_T;
    v_contact_obj JSON_OBJECT_T;
    v_name VARCHAR2(100);
    v_email VARCHAR2(100);
BEGIN
    -- 规范将BLOB转换为CLOB，指定UTF8字符集避免乱码
    DBMS_LOB.CONVERTTOCLOB(clob_variable, blob_body, DBMS_LOB.LOBMAXSIZE, 0, 0, NLS_CHARSET_ID('UTF8'));
    
    -- 解析顶层JSON对象
    v_json_obj := JSON_OBJECT_T.parse(clob_variable);
    
    -- 获取嵌套的contact子对象
    v_contact_obj := v_json_obj.get_Object('contact');
    
    -- 从contact对象中提取目标字段
    v_name := v_contact_obj.get_String('name');
    v_email := v_contact_obj.get_String('email');

    -- 使用序列生成唯一ID，避免硬编码导致主键重复
    INSERT INTO LEADS (ID, NOME, EMAIL)
    VALUES (LEADS_SEQ.NEXTVAL, v_name, v_email) -- 替换为你的实际序列名
    RETURNING ID INTO new_id;

    :status_code := 201;
    :forward_location := '../leads/' || new_id; -- 路径与表名保持语义一致

EXCEPTION
    WHEN JSON_EXCEPTION THEN
        :errmsg := 'JSON解析错误: ' || SQLERRM;
        :status_code := 400;
    WHEN VALUE_ERROR THEN
        :errmsg := '字段值不符合要求: ' || SQLERRM;
        :status_code := 400;
    WHEN DUP_VAL_ON_INDEX THEN
        :errmsg := '该邮箱已存在';
        :status_code := 409;
    WHEN OTHERS THEN 
        :status_code := 500; -- 服务器内部错误使用标准500状态码
        :errmsg := '系统错误: ' || SQLERRM;
END;

修改说明：

字段路径修正：原代码直接从顶层JSON取name和email，但这两个字段属于contact子对象，必须先通过get_Object('contact')获取子对象，再提取字段。
BLOB转CLOB规范：改用Oracle官方DBMS_LOB.CONVERTTOCLOB并指定UTF8字符集，避免自定义函数或编码问题导致解析失败。
ID生成优化：移除硬编码ID，改用序列自动生成唯一主键，避免主键冲突。
异常处理增强：新增JSON解析、唯一约束冲突等针对性异常捕获，返回更明确的错误信息，HTTP状态码符合规范。
代码结构优化：移除不必要的嵌套块，变量作用域更清晰，路径语义与业务表名统一。

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

如何将Webhook传入的JSON数据插入Oracle APEX的表中

修改说明： #

修改说明：