Oracle 19c中PL/SQL存储过程用JSON_OBJECT(*)加变量触发ORA-00904错误

问题复现 #

1. 创建测试表并插入数据 #

create table JSON_TABLE (
    ID      NUMBER,
    COLUMN1 VARCHAR(100),
    COLUMN2 VARCHAR(100),
    COLUMN3 VARCHAR(100)
);
/
create table JSON_RESAULT_TABLE (
    ID          NUMBER,
    J_OBJECT    VARCHAR2(4000)
);
/

INSERT INTO JSON_TABLE VALUES (1, 'Random text 1', 'Value 1', 'Example data 1');
INSERT INTO JSON_TABLE VALUES (2, 'Random text 2', 'Value 2', 'Example data 2');
INSERT INTO JSON_TABLE VALUES (1, 'Random text 3', 'Value 3', 'Example data 3');
INSERT INTO JSON_TABLE VALUES (2, 'Random text 4', 'Value 4', 'Example data 4');
INSERT INTO JSON_TABLE VALUES (1, 'Random text 5', 'Value 5', 'Example data 5');
INSERT INTO JSON_TABLE VALUES (2, 'Random text 6', 'Value 6', 'Example data 6');
INSERT INTO JSON_TABLE VALUES (1, 'Random text 7', 'Value 7', 'Example data 7');
INSERT INTO JSON_TABLE VALUES (2, 'Random text 8', 'Value 8', 'Example data 8');
INSERT INTO JSON_TABLE VALUES (1, 'Random text 9', 'Value 9', 'Example data 9');
INSERT INTO JSON_TABLE VALUES (2, 'Random text 10', 'Value 10', 'Example data 10');
/

2. 触发错误的存储过程 #

执行以下存储过程时抛出ORA-00904: "V_ID_2": invalid identifier错误：

create or replace procedure p_json_test (v_id_1 NUMBER, v_id_2 NUMBER) is
begin
    INSERT INTO     JSON_RESAULT_TABLE
    SELECT          ID, JSON_OBJECT(*)
    FROM            JSON_TABLE
    WHERE           ID IN (v_id_1, v_id_2);
end;
/
execute p_json_test(1, 2);

3. 正常运行的两种情况 #

• 显式指定JSON_OBJECT列时存储过程正常：

create or replace procedure p_json_test (v_id_1 NUMBER, v_id_2 NUMBER) is
begin
    INSERT INTO     JSON_RESAULT_TABLE
    SELECT ID, 
           JSON_OBJECT(
               'COLUMN1' VALUE COLUMN1,
               'COLUMN2' VALUE COLUMN2,
               'COLUMN3' VALUE COLUMN3)
    FROM            JSON_TABLE
    WHERE           ID IN (v_id_1, v_id_2);
end;
/

• WHERE子句硬编码ID时存储过程正常：

create or replace procedure p_json_test (v_id_1 NUMBER, v_id_2 NUMBER) is
begin
    INSERT INTO     JSON_RESAULT_TABLE
    SELECT          ID, JSON_OBJECT(*)
    FROM            JSON_TABLE
    WHERE           ID IN (1, 2);
end;
/

错误原因 #

这是Oracle 19c版本中JSON_OBJECT(*)的解析bug：当在PL/SQL静态SQL语句中同时使用JSON_OBJECT(*)和PL/SQL绑定变量时，SQL解析器会错误地将WHERE子句中的绑定变量（如v_id_2）识别为表的列名，试图将其纳入JSON_OBJECT(*)要序列化的列列表中，最终因该变量并非表列而抛出无效标识符错误。

解决方法 #

以下是无需逐个指定列名即可将整行转为JSON对象的可行方案：

方案1：使用ROWTOJSON + JSON_OBJECT_T #

利用ROWTOJSON将整行转为JSON格式的CLOB，再通过JSON_OBJECT_T转换为字符串，避开JSON_OBJECT(*)的解析问题：

create or replace procedure p_json_test (v_id_1 NUMBER, v_id_2 NUMBER) is
begin
    INSERT INTO JSON_RESAULT_TABLE
    SELECT ID, JSON_OBJECT_T(ROWTOJSON(jt)).to_string()
    FROM JSON_TABLE jt
    WHERE ID IN (v_id_1, v_id_2);
end;
/
execute p_json_test(1, 2);

方案2：使用动态SQL #

通过动态SQL拼接查询语句，动态SQL的解析逻辑不会将绑定变量误判为表列：

create or replace procedure p_json_test (v_id_1 NUMBER, v_id_2 NUMBER) is
    v_sql varchar2(1000);
begin
    v_sql := 'INSERT INTO JSON_RESAULT_TABLE
              SELECT ID, JSON_OBJECT(*)
              FROM JSON_TABLE
              WHERE ID IN (:1, :2)';
    execute immediate v_sql using v_id_1, v_id_2;
end;
/
execute p_json_test(1, 2);

注意：动态SQL需注意防范SQL注入风险，此处使用绑定变量已规避该问题。

方案3：升级Oracle版本 #

该bug在Oracle 21c及更高版本中已被官方修复，若条件允许可升级数据库版本彻底解决问题。

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