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

CodePudding user response：

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