How to insert a json object with ORACLE 19 and 21

Because I don't use Oracle 21. I can't use the JSON type in the definition of a table.

CREATE TABLE TABLE_TEST_QUERY_2

    (
        TTQ_NR                    INTEGER GENERATED BY DEFAULT AS IDENTITY,
        TTQ_QUERY_TO_BE_TESTED    VARCHAR2 (4000 BYTE),
        TTQ_RESULT                CLOB,
        --RESULT              JSON, UPGRADE oracle 21
        TTQ_TTQ_CREATION_DATE     DATE DEFAULT SYSDATE,
        TTQ_ALREADY_TESTED        INTEGER DEFAULT 0,
        TTQ_TEST_PASSED           INTEGER,
        PRIMARY KEY (TTQ_NR),
        CONSTRAINT RESULT CHECK (TTQ_RESULT IS JSON)
    )

I want to add a json object in ttq_result. Not a string representing a json. I've a way to transform a json into a clob.

select to_clob(utl_raw.cast_to_raw (json_object('a' value 2))) from dual;

But it's not working, if I try to insert the clob created from a json in the table

INSERT INTO BV_OWN.TABLE_TEST_QUERY_2 TTQ_RESULT
     VALUES to_clob(utl_raw.cast_to_raw (json_object(a value '2')));

[Error] Execution (3: 13): ORA-03001: unimplemented feature

code(oracle 18)

---

update:

I've tried to add a json on dbfiddle with oracle 21. I'm using the json type to define a column.

CREATE TABLE TABLE_TEST_QUERY_2
(
    TTQ_NR                    INTEGER GENERATED BY DEFAULT AS IDENTITY,
    TTQ_QUERY_TO_BE_TESTED    VARCHAR2 (4000 BYTE),
    TTQ_RESULT                JSON, 
    TTQ_TTQ_CREATION_DATE     DATE DEFAULT SYSDATE,
    TTQ_ALREADY_TESTED        INTEGER DEFAULT 0,
    TTQ_TEST_PASSED           INTEGER,
    PRIMARY KEY (TTQ_NR)

)

INSERT INTO TABLE_TEST_QUERY_2 TTQ_RESULT VALUES json_object('a' value 2);

I have the same error.

ORA-03001: unimplemented feature

Maybe are these 2 problems related.

code oracle 21

Your first problem is because you are using the wrong syntax as you have omitted the brackets from around column identifiers or the column value:

INSERT INTO BV_OWN.TABLE_TEST_QUERY_2 (TTQ_RESULT)
  VALUES ( to_clob(utl_raw.cast_to_raw (json_object(a value '2'))));

Which fixes the unimplemented feature exception but now you get:

ORA-00984: column not allowed here

Which is because you are using a different query to the SELECT as you have changed json_object('a' value 2) to json_object(a value '2') and the query cannot find a column a.

If you fix that by using the original code from the SELECT with 'a' as a string literal and not a a column identifier:

INSERT INTO BV_OWN.TABLE_TEST_QUERY_2 (TTQ_RESULT)
  VALUES ( to_clob(utl_raw.cast_to_raw (json_object('a' value 2))));

You will then get the error:

ORA-02290: check constraint (FIDDLE_FCJHJVMCPHKXUCUPDUSV.RESULT) violated

Because converting to a RAW and then to a CLOB will mangle the value.

You need something much simpler:

INSERT INTO BV_OWN.TABLE_TEST_QUERY_2 (TTQ_RESULT)
  VALUES (json_object('a' value 2));

or:

INSERT INTO BV_OWN.TABLE_TEST_QUERY_2 (TTQ_RESULT)
  VALUES (EMPTY_CLOB() || json_object('a' value 2));

Which both work.

db<>fiddle here