动态将JSON数据插入Oracle表时Execute Immediate取值异常求助

错误原因 #

你遇到的ORA-00984错误，本质是拼接INSERT语句时，VALUES子句里用了JSON列名（如column1）而非实际值，Oracle会把这些列名当成表的字段，但目标表oracle_tbl中并没有这些字段，因此报错。

推荐解决方案：批量动态插入（高效安全） #

直接通过动态生成INSERT ... SELECT语句，将JSON_TABLE作为数据源，结合lookup表的映射关系批量插入，避免循环拼接值的低效和风险：

DECLARE
  v_insert_sql   VARCHAR2(4000);
  v_table_cols   VARCHAR2(2000);
  v_json_select  VARCHAR2(2000);
  v_json_columns VARCHAR2(2000);
BEGIN
  -- 1. 从lookup表组装目标表列、JSON查询列和JSON_TABLE的列定义
  SELECT 
    LISTAGG(table_column, ', ') WITHIN GROUP (ORDER BY table_column),
    LISTAGG('j.' || json_column, ', ') WITHIN GROUP (ORDER BY table_column),
    LISTAGG(json_column || ' NUMBER PATH ''$.' || json_column || '''', ', ') WITHIN GROUP (ORDER BY table_column)
  INTO v_table_cols, v_json_select, v_json_columns
  FROM json_column_mapping;

  -- 2. 动态生成批量插入SQL
  v_insert_sql := 'INSERT INTO oracle_tbl (' || v_table_cols || ') ' ||
                  'SELECT ' || v_json_select || ' ' ||
                  'FROM JSON_TABLE(:p_json_data, ''$.ExcelData[*]'' ' ||
                  'COLUMNS (' || v_json_columns || ')) j';

  -- 3. 执行动态SQL，传入原始JSON参数
  EXECUTE IMMEDIATE v_insert_sql USING PJSON_DATA;
  COMMIT;
END;
/

说明： #

• 该方法直接从JSON_TABLE中读取实际值插入目标表，完全避免了值拼接的问题
• 自动适配lookup表的映射关系，无需硬编码列名
• 批量插入比循环单条插入效率高得多

备选方案：循环单条插入（适合需逐行处理场景） #

如果必须逐行处理JSON数据（比如需要添加业务校验），可以通过动态SQL从循环记录中提取对应JSON列的实际值：

DECLARE
  v_table_cols VARCHAR2(2000);
  v_val_list   VARCHAR2(2000);
  v_current_val VARCHAR2(4000);
BEGIN
  -- 从lookup表获取目标表列列表
  SELECT LISTAGG(table_column, ', ') INTO v_table_cols FROM json_column_mapping;

  -- 循环读取JSON数据
  FOR all_rec1 IN (SELECT * FROM JSON_TABLE(PJSON_DATA, '$.ExcelData[*]'
                                            COLUMNS ( col1 NUMBER PATH '$.col1',
                                                      col2 NUMBER PATH '$.col2' -- 注意修正你原代码中的PATH笔误
                                                    ))) LOOP
    v_val_list := '';
    -- 遍历lookup表的JSON列，提取当前记录的对应值
    FOR json_col_rec IN (SELECT json_column FROM json_column_mapping) LOOP
      -- 动态获取当前记录中对应JSON列的实际值
      EXECUTE IMMEDIATE 'SELECT :rec.' || json_col_rec.json_column || ' FROM DUAL'
      INTO v_current_val USING all_rec1;

      -- 组装值列表（数字无需加引号，字符串需额外处理引号转义）
      v_val_list := CASE WHEN v_val_list IS NOT NULL THEN v_val_list || ', ' ELSE '' END || v_current_val;
    END LOOP;

    -- 生成并执行单条插入语句
    EXECUTE IMMEDIATE 'INSERT INTO oracle_tbl (' || v_table_cols || ') VALUES (' || v_val_list || ')';
  END LOOP;
  COMMIT;
END;
/

注意事项： #

• 原代码中Column2的PATH写为$.col1是笔误，需修正为对应JSON字段（如$.col2）
• 如果JSON包含字符串类型字段，需要给值添加单引号并处理转义（比如把'替换为''），避免SQL语法错误和注入风险

内容的提问来源于stack exchange，提问作者Monica Augustine-Plsql Newbie