Oracle APEX Ajax回调PL/SQL异常：误删全表数据排查

这不是Oracle APEX的Bug，核心问题是PL/SQL变量名与表列名重名导致的标识符解析错误。

原因分析 #

Oracle在解析PL/SQL块中的嵌入式SQL时，会优先将同名标识符解析为PL/SQL局部变量，而非表的列名。你的DELETE语句中：

DELETE FROM CHLI_IMAGES WHERE (INSPECTION_ID = inspection_id AND INNER_ID = inner_id);

这里的INSPECTION_ID和inner_id会被Oracle识别为你声明的PL/SQL变量，导致条件变成「变量=变量」（永远为真），最终等价于执行DELETE FROM CHLI_IMAGES;，所以删除了表中所有行。

而静态SQL能正常工作，是因为静态SQL中没有同名变量，Oracle直接将标识符解析为表列，条件逻辑正确。

解决方案 #

有两种简单的修复方式：

1. 修改PL/SQL变量名，避免与列名冲突

遵循PL/SQL编码规范，给局部变量添加前缀（比如l_表示local），区分表列：

DECLARE
    l_inspection_id number;
    l_inner_id number;
BEGIN
    l_inspection_id := apex_application.g_x01;
    l_inner_id := apex_application.g_x02;

    apex_debug.info('=====================================');
    apex_debug.info('DELETE ENTRY WITH INSPECTION_ID: '||l_inspection_id||' AND INNER_ID: '||l_inner_id);
    DELETE FROM CHLI_IMAGES WHERE (INSPECTION_ID = l_inspection_id AND INNER_ID = l_inner_id);
    apex_debug.info('ROWS DELETED '|| SQL%ROWCOUNT);

    apex_json.open_object;
    apex_json.write('success', true);
    apex_json.write('message', '删除完成');
    apex_json.write('INSPECTION_ID', l_inspection_id);
    apex_json.write('INNER_ID', l_inner_id);
    apex_json.write('result', true);
    apex_json.close_object;
EXCEPTION
    WHEN OTHERS THEN
        apex_json.open_object;
        apex_json.write('success', false);
        apex_json.write('message', sqlerrm);
        apex_json.close_object;
END;

2. 在SQL中使用表别名明确引用列

通过表别名指定列归属，避免标识符混淆：

DECLARE
    inspection_id number;
    inner_id number;
BEGIN
    inspection_id := apex_application.g_x01;
    inner_id := apex_application.g_x02;

    apex_debug.info('=====================================');
    apex_debug.info('DELETE ENTRY WITH INSPECTION_ID: '||inspection_id||' AND INNER_ID: '||inner_id);
    -- 添加表别名img，用img.INSPECTION_ID明确指定表列
    DELETE FROM CHLI_IMAGES img 
    WHERE (img.INSPECTION_ID = inspection_id AND img.INNER_ID = inner_id);
    apex_debug.info('ROWS DELETED '|| SQL%ROWCOUNT);

    apex_json.open_object;
    apex_json.write('success', true);
    apex_json.write('message', '删除完成');
    apex_json.write('INSPECTION_ID', inspection_id);
    apex_json.write('INNER_ID', inner_id);
    apex_json.write('result', true);
    apex_json.close_object;
EXCEPTION
    WHEN OTHERS THEN
        apex_json.open_object;
        apex_json.write('success', false);
        apex_json.write('message', sqlerrm);
        apex_json.close_object;
END;

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