如何获取INSERT语句中出错的列编号以提示Excel导入用户

要在APEX导入Excel并插入数据时精准定位错误列，可通过预校验+错误捕获+前端展示的流程实现，具体步骤如下：

1. 存储Excel解析数据到临时表 #

先将apex_data_parser解析后的Excel数据（包含原始行号）存入临时表，方便后续校验和错误追踪：

CREATE GLOBAL TEMPORARY TABLE temp_excel_import (
    row_num NUMBER, -- 对应Excel的行号
    col001 VARCHAR2(100), -- Excel第1列：员工编号（empno来源）
    col002 VARCHAR2(100), -- Excel第2列：员工姓名（ename来源）
    dept_code VARCHAR2(100), -- Excel第3列：部门编码（关联dept表）
    error_msg VARCHAR2(4000) -- 存储错误信息
) ON COMMIT DELETE ROWS;

通过apex_data_parser将Excel数据插入此表时，需保留解析返回的row_num字段（对应Excel原始行号）。

2. 逐字段校验并捕获错误 #

编写PL/SQL块遍历临时表，对每个字段单独校验，捕获类型错误、关联错误等，并记录具体出错位置：

DECLARE
    v_empno NUMBER;
    v_deptno NUMBER;
BEGIN
    FOR rec IN (SELECT row_num, col001, col002, dept_code FROM temp_excel_import) LOOP
        -- 校验Excel第1列（员工编号）是否为有效数字
        BEGIN
            v_empno := TO_NUMBER(rec.col001);
        EXCEPTION
            WHEN VALUE_ERROR THEN
                UPDATE temp_excel_import 
                SET error_msg = '第' || rec.row_num || '行，第1列（员工编号）：无效数字格式'
                WHERE row_num = rec.row_num;
                CONTINUE;
        END;

        -- 校验Excel第3列（部门编码）是否存在于dept表
        BEGIN
            SELECT deptno INTO v_deptno 
            FROM dept WHERE code = rec.dept_code;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                UPDATE temp_excel_import 
                SET error_msg = '第' || rec.row_num || '行，第3列（部门编码）：不存在该部门编码'
                WHERE row_num = rec.row_num;
                CONTINUE;
        END;

        -- 校验通过则插入正式表
        INSERT INTO emp(empno, ename, deptno)
        VALUES(v_empno, rec.col002, v_deptno);
    END LOOP;
    COMMIT;
END;
/

3. 展示错误信息给终端用户 #

执行完校验和插入逻辑后，查询临时表中的错误记录，在APEX页面通过交互式报表或动态内容区域展示给用户：

SELECT error_msg FROM temp_excel_import WHERE error_msg IS NOT NULL;

可将查询结果拼接成列表样式，让用户直观看到每一行的错误位置和原因。

4. 批量插入的错误捕获（可选） #

如果需要批量处理数据，可使用FORALL ... SAVE EXCEPTIONS捕获批量插入的异常，根据错误代码定位对应列：

DECLARE
    TYPE emp_rec_type IS RECORD (
        empno NUMBER,
        ename VARCHAR2(100),
        deptno NUMBER,
        row_num NUMBER
    );
    TYPE emp_tab_type IS TABLE OF emp_rec_type;
    v_emp_tab emp_tab_type;
    v_error_idx NUMBER;
BEGIN
    -- 收集校验后的有效数据
    SELECT TO_NUMBER(col001), col002, 
           (SELECT deptno FROM dept WHERE code = dept_code),
           row_num
    BULK COLLECT INTO v_emp_tab
    FROM temp_excel_import
    WHERE error_msg IS NULL;

    -- 批量插入并捕获异常
    FORALL i IN v_emp_tab.FIRST..v_emp_tab.LAST SAVE EXCEPTIONS
        INSERT INTO emp(empno, ename, deptno)
        VALUES(v_emp_tab(i).empno, v_emp_tab(i).ename, v_emp_tab(i).deptno);
EXCEPTION
    WHEN OTHERS THEN
        v_error_idx := SQL%BULK_EXCEPTIONS.FIRST;
        WHILE v_error_idx IS NOT NULL LOOP
            -- 根据错误码判断出错列：ORA-01722对应无效数字（empno列）
            IF SQL%BULK_EXCEPTIONS(v_error_idx).ERROR_CODE = -1722 THEN
                UPDATE temp_excel_import
                SET error_msg = '第' || v_emp_tab(v_error_idx).row_num || '行，第1列（员工编号）：无效数字格式'
                WHERE row_num = v_emp_tab(v_error_idx).row_num;
            END IF;
            v_error_idx := SQL%BULK_EXCEPTIONS.NEXT(v_error_idx);
        END LOOP;
        COMMIT;
END;
/

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