Is there a way/workaround to attach clob to a JSON element?-CodePudding

I'm trying to add a json and add a clob to it. On the Oracle website, it says that the json_object_t.put function only supports varchar, just wondering if anyone knows a workaround?

The database version we are using is 12c, but we might migrate to 21c

declare
    j       json_object_t;
    v_clob  clob; -- > 32767 characters
begin
    j:= json_object_t;
    j.put(v_clob);
end;

CodePudding user response：

The string length of a varchar2 variable could already be upto 32,767 within PL/SQL unlike to the upper bound 4,000 which takes place in a table creation DDL statement.

So, You can convert v_clob clob to v_str varchar2(32767) and should invoke by adding missing first argument(key) of type varchar2 as well,conforming to a set procedure syntax:

MEMBER PROCEDURE put(key VARCHAR2, value JSON_ELEMENT_T)

CodePudding user response：

You should create a JSON string '{"key_of_clob": "', then concat the content of the CLOB, ending by adding '"}' to obtain a CLOB containing a valid JSON expression, you could then parse it to create a JSON object: json_object_t.parse(my_temp_json_clob); and eventually you can add this one to the final JSON object using put(key VARCHAR2, value JSON_ELEMENT_T). Don't forget to free the temporary CLOB used.

see https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9535894000346566772