Nsryjdtyk

I'm trying to extract a very long string into clob from json_object_t and got some weird database behaviour (12.2c) with json_object_t.get_clob(key) method.
There is a sample code than does following:

DECLARE

  l_data CLOB := '{"text": "very long string about 1M chars"}';

  l_json json_object_t;

  l_text CLOB := EMPTY_CLOB();

BEGIN

  l_json := json_object_t.parse(l_data);

  l_text := l_json.get_clob('text');

  dbms_output.put_line('got ' || dbms_lob.getlength(l_text) || ' chars');

END;

When string length in a 'text' key is less than 32k chars, get_clob method works just fine and shows appropriate result, but with longer strings it produces an empty clob with zero length, just like get_string, but without 'character string buffer too small' exception.

I've tried to get same data via json_table query, but it cannot extract data to clob column at all, only varchar/number is allowed.

Is that a bug or am I doing something wrong? Is there any other ways to extract long strings from JSON keys?

I work with Oracle Database JSON Store group and would be happy to assist you with this issue you're facing. Could you try the alternate get_Clob procedure instead of this function and tell us what the behavior is?

Signature:

MEMBER PROCEDURE get_Clob(key VARCHAR2, c IN OUT CLOB)

Please try this:

DECLARE

    content_json CLOB := '{"value":"';

    content_json_end CLOB := '"}';

    content_tmp CLOB := 'ab';

    l_json json_object_t;

     l_text CLOB := EMPTY_CLOB();

    tmp clob;

BEGIN

    -- 13 gives 16K

    -- 14 gives 32K

    FOR count IN 1 .. 14

    loop

        dbms_lob.append(content_tmp, content_tmp);  -- a bad append for now

    END loop;



    dbms_lob.append(content_json, content_tmp);

    dbms_lob.append(content_json, content_json_end);



    l_json := json_object_t.parse(content_json);

    l_json.get_clob('value', l_text); -- !!! TRY THIS PROC get_Clob

    --l_text := l_json.get_clob('value');



    dbms_output.put_line('Lob size in Kb: ');

    dbms_output.put_line(dbms_lob.getLength(l_text) / 1024);

END;

/ 

Looking forward to your findings..

This works as well. Instead using the the get_clob method, use c:

DECLARE



   CURSOR crsrJSON IS

   SELECT 

       json_object( 'employee_id'     VALUE employee_id,

                    'first_name'      VALUE first_name,

                    'last_name'       VALUE last_name,

                    'email'           VALUE email,

                    'phone_number'    VALUE phone_number,

                    'hire_date'       VALUE to_char(hire_date,'MM/DD/YYYY'),

                    'job_id'          VALUE job_id,

                    'salary'          VALUE nvl(salary,0),

                    'commission_pct'  VALUE nvl(commission_pct,0),

                    'manager_id'      VALUE NVL(manager_id,0),

                    'department_id'   VALUE NVL(department_id,0), 

                    'department_name' VALUE (select department_name from departments x where x.department_id = hr.department_id), 

                    'job_title'       VALUE (select job_title from jobs x where x.job_id = hr.job_id)) emp_data

    FROM 

           employees hr;



    js_array JSON_ARRAY_T := new JSON_ARRAY_T;



    json_obj  JSON_OBJECT_T := JSON_OBJECT_T();



    json_clob CLOB := EMPTY_CLOB();



BEGIN



   FOR data_rec IN crsrJSON LOOP



     js_array.append(JSON_ELEMENT_T.parse(data_rec.emp_data));



   END LOOP;



   json_obj.put('data',js_array);







   IF json_obj.has('data') THEN



      json_clob := json_obj.to_clob;

      DBMS_OUTPUT.PUT_LINE(json_clob);





   ELSE



     DBMS_OUTPUT.PUT_LINE('Nope');



   END IF;



END;