Apex交互式网格自定义DML：数据无法保存至目标表问题

问题背景 #

未使用集合的情况下，编写了PL/SQL代码用于将Apex交互式网格（IG）前端数据保存至RMA_USER_EVALUATIONS表，代码如下：

DECLARE
    l_id_risk NUMBER := :P12_ID_RISK;
    l_subtype VARCHAR2(100) := :P12_SUBTYPE;
    l_evaluator VARCHAR2(100) := :P12_EVALUATOR;
    l_supplier VARCHAR2(100) := :P12_SUPPLIER;
    l_row_exists NUMBER;
BEGIN
    -- Check if the row exists in RMA_USER_EVALUATIONS
    SELECT COUNT(*)
    INTO l_row_exists
    FROM RMA_USER_EVALUATIONS
    WHERE ID_RISK = l_id_risk
        AND SUBTYPE = l_subtype
        AND QUESTION = :Question
        AND EVALUATOR = l_evaluator
        AND SUPPLIER = l_supplier;

    IF l_row_exists > 0 THEN
        -- Update the existing row
        IF :Response IS NULL THEN
            -- Delete the row if "Response" is set to NULL
            DELETE FROM RMA_USER_EVALUATIONS
            WHERE ID_RISK = l_id_risk
                AND SUBTYPE = l_subtype
                AND QUESTION = :Question
                AND EVALUATOR = l_evaluator
                AND SUPPLIER = l_supplier;
        ELSE
            UPDATE RMA_USER_EVALUATIONS
            SET ANSWER = :Response,
                COMMENTS = :Comments
            WHERE ID_RISK = l_id_risk
                AND SUBTYPE = l_subtype
                AND QUESTION = :Question
                AND EVALUATOR = l_evaluator
                AND SUPPLIER = l_supplier;
        END IF;
    ELSE
        -- Insert a new row
        INSERT INTO RMA_USER_EVALUATIONS (ID_RISK, SUBTYPE, QUESTION, ANSWER, COMMENTS, EVALUATOR, SUPPLIER)
        VALUES (l_id_risk, l_subtype, :Question, :Response, :Comments, l_evaluator, l_supplier);
    END IF;
END;

IG前端展示的数据结构：

Question    Response    Comments
How sustainable are the supplier's sourcing and supply chain practices? Yes Test1
How sustainable are the supplier's sourcing and supply chain practices?2    No  Test2
How sustainable are the supplier's sourcing and supply chain practices?3    Yes Test3
How sustainable are the supplier's sourcing and supply chain practices?5    No  Test4
How sustainable are the supplier's sourcing and supply chain practices?4    Yes Test5

IG初始查询语句：

SELECT ID,
    QUESTION,
    WEIGHT AS RESPONSE,
    COMMENTS
FROM RMA_QUESTIONS_TYPE
WHERE SUBTYPE = :P12_SUBTYPE

后续更新为关联RMA_USER_EVALUATIONS的查询：

SELECT q.ID,
       q.QUESTION,
       (SELECT ue.ANSWER
        FROM RMA_USER_EVALUATIONS ue
        WHERE ue.ID_RISK = :P12_ID_RISK
          AND ue.SUBTYPE = :P12_SUBTYPE
          AND ue.EVALUATOR = :P12_EVALUATOR
          AND ue.SUPPLIER = :P12_SUPPLIER
          AND ue.QUESTION = q.QUESTION) AS RESPONSE,
       (SELECT ue.COMMENTS
        FROM RMA_USER_EVALUATIONS ue
        WHERE ue.ID_RISK = :P12_ID_RISK
          AND ue.SUBTYPE = :P12_SUBTYPE
          AND ue.EVALUATOR = :P12_EVALUATOR
          AND ue.SUPPLIER = :P12_SUPPLIER
          AND ue.QUESTION = q.QUESTION) AS COMMENTS
FROM RMA_QUESTIONS_TYPE q
WHERE q.SUBTYPE = :P12_SUBTYPE

问题描述 #

已启用IG相关属性并生成保存按钮，但用户修改IG数据后点击保存，数据无法存入RMA_USER_EVALUATIONS表。该表字段包括ID_RIESGO、SUBTYPE、QUESTION、ANSWER、COMMENTS、EVALUATOR、PROVIDER ID，需要将P12_SUBTYPE填入SUBTYPE字段，P12_EVALUATOR填入EVALUATOR字段，P12_ID_RISK填入ID_RISK字段，同时将IG中填写的Question、Response、Comments对应存入QUESTION、ANSWER、COMMENTS字段。

1. 修正字段名不匹配问题 #

代码中使用的SUPPLIER字段与目标表实际字段PROVIDER ID（推测实际字段名为PROVIDER_ID，Oracle不建议字段名含空格）不匹配，这是核心问题。需将代码中所有SUPPLIER替换为正确字段名，并调整对应页面项变量：

DECLARE
    l_id_risk NUMBER := :P12_ID_RISK;
    l_subtype VARCHAR2(100) := :P12_SUBTYPE;
    l_evaluator VARCHAR2(100) := :P12_EVALUATOR;
    l_provider_id VARCHAR2(100) := :P12_PROVIDER_ID; -- 替换为对应供应商页面项
    l_row_exists NUMBER;
BEGIN
    -- 检查行是否存在
    SELECT COUNT(*)
    INTO l_row_exists
    FROM RMA_USER_EVALUATIONS
    WHERE ID_RISK = l_id_risk
        AND SUBTYPE = l_subtype
        AND QUESTION = :QUESTION -- 匹配IG列的静态ID大小写
        AND EVALUATOR = l_evaluator
        AND PROVIDER_ID = l_provider_id; -- 替换为表实际字段名

    IF l_row_exists > 0 THEN
        -- 更新或删除现有行
        IF :RESPONSE IS NULL THEN
            DELETE FROM RMA_USER_EVALUATIONS
            WHERE ID_RISK = l_id_risk
                AND SUBTYPE = l_subtype
                AND QUESTION = :QUESTION
                AND EVALUATOR = l_evaluator
                AND PROVIDER_ID = l_provider_id;
        ELSE
            UPDATE RMA_USER_EVALUATIONS
            SET ANSWER = :RESPONSE,
                COMMENTS = :COMMENTS
            WHERE ID_RISK = l_id_risk
                AND SUBTYPE = l_subtype
                AND QUESTION = :QUESTION
                AND EVALUATOR = l_evaluator
                AND PROVIDER_ID = l_provider_id;
        END IF;
    ELSE
        -- 仅当Response非空时插入新行
        IF :RESPONSE IS NOT NULL THEN
            INSERT INTO RMA_USER_EVALUATIONS (ID_RISK, SUBTYPE, QUESTION, ANSWER, COMMENTS, EVALUATOR, PROVIDER_ID)
            VALUES (l_id_risk, l_subtype, :QUESTION, :RESPONSE, :COMMENTS, l_evaluator, l_provider_id);
        END IF;
    END IF;
END;

2. 确认IG的DML配置 #

• 进入IG的编辑属性页，在处理区域选择自定义PL/SQL处理。
• 将修正后的代码配置到插入、更新、删除对应的PL/SQL块中（或使用统一的行处理逻辑）。
• 确保IG可编辑列（Response、Comments）的静态ID与代码中绑定变量完全一致（Apex绑定变量大小写敏感，比如列静态ID为RESPONSE，绑定变量需为:RESPONSE）。

3. 验证绑定变量与页面项 #

• 检查:P12_ID_RISK、:P12_SUBTYPE、:P12_EVALUATOR、:P12_PROVIDER_ID等页面项是否存在且值正确，可通过Apex调试模式查看。
• 确认IG的Question列为只读状态，且绑定变量:QUESTION能正确获取该列值。

4. 调试与错误排查 #

• 启用Apex调试模式，点击保存后查看日志，排查SQL错误（如字段不存在、约束违反）。
• 在PL/SQL代码中添加日志输出（如DBMS_OUTPUT.PUT_LINE('QUESTION值: ' || :QUESTION);），通过Apex的DBMS_OUTPUT窗口验证变量值。

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