I’m encountering an issue when using the JSON_OBJECT function inside a PL/SQL block. Below is an example of a PL/SQL procedure that triggers an unexpected error:

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');
/

--Here is the PL/SQL procedure that causes the error:
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);

When I execute the above procedure, I get the following error:

ORA-00904: "V_ID_2": invalid identifier

However, if I explicitly specify the columns in the JSON_OBJECT function, the procedure works without any issues:

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;
/

Additionally, if I hardcode the IDs in the WHERE clause instead of using variables, the procedure also works fine:

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;
/

I’m using Oracle Database 19c. Does anyone know why this issue occurs when using variables in the WHERE clause with JSON_OBJECT(*)? Is there a workaround to dynamically convert all columns into a JSON object without explicitly listing each column?

I’m encountering an issue when using the JSON_OBJECT function inside a PL/SQL block. Below is an example of a PL/SQL procedure that triggers an unexpected error:

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');
/

--Here is the PL/SQL procedure that causes the error:
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);

When I execute the above procedure, I get the following error:

ORA-00904: "V_ID_2": invalid identifier

However, if I explicitly specify the columns in the JSON_OBJECT function, the procedure works without any issues:

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;
/

Additionally, if I hardcode the IDs in the WHERE clause instead of using variables, the procedure also works fine:

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;
/

I’m using Oracle Database 19c. Does anyone know why this issue occurs when using variables in the WHERE clause with JSON_OBJECT(*)? Is there a workaround to dynamically convert all columns into a JSON object without explicitly listing each column?

• json
• oracle-database
• plsql
• oracle19c

• Your code works in Oracle 21 fiddle. – MT0 Commented Mar 10 at 16:07
• It works in 19c too. One thing you haven't shown in the above code, however, is your actual procedure call. We'd want to see if you are using named or positional parameters, and what that call looks like. Also, your DDL for JSON_TABLE is incorrect, as your sample data overflows the 10 character limit you have set. – Paul W Commented Mar 10 at 20:00
• Thank @PaulW for your response and for pointing out the incorrect column size in the sample data I provided. I coreccted it. In my example, I used positional parameters as follows: execute p_json_test(1, 2); However the issue is unrelated to how the parameters were passed. The problem arises from the interaction between JSON_OBJECT(*) and the bind variables in the WHERE clause. It triggers the ORA-00904 error. – Kamil Raszkowski Commented Mar 11 at 6:50

1 Answer 1

As a workaround I manage to use dynamic sql:

CREATE OR REPLACE PROCEDURE p_json_test(v_id_1 NUMBER, v_id_2 NUMBER) IS
    v_sql VARCHAR2(4000);
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);

This approach solve an issue