My Oracle Support Banner

Unexpected Behavior With JSON_ARRAY_T - with Null values (Doc ID 2683172.1)

Last updated on JULY 12, 2020

Applies to:

Oracle Database - Enterprise Edition - Version and later
Information in this document applies to any platform.


Customer should know that the original reason it's failing is because they're doing something that, technically, isn't allowed by PL/SQL, but PL/SQL is quietly ignoring the problem due to an old bug.
Specifically, according to the PL/SQL team, NULL object type instances (in this example, an instance of Json_Array_T) are not allowed. The reason is complex but has something to do with not being able to run the constructor. So, technically, they should have gotten an error at that point and the entire example isn't expected to work.

The correct way to do this is to use set_Null or append_Null to put null values into the JSON.

  v_js_obj json_object_t;
  v_js_arr json_array_t := json_array_t();
  function get_js_arr(n number)
  return json_array_t
  if (n = 1) then
  return json_array_t.parse('[1, 2, 3]');
  return null /*json_array_t()*/;
  end if;
  for i in 1 .. 2 loop
  v_js_obj := json_object_t.parse('{}');
  dbms_output.put_line('v_js_obj before put: ' || v_js_obj.to_clob);
  v_js_arr := get_js_arr(i);
  if (v_js_arr.is_null()) then
  dbms_output.put_line( 'v_js_arr is null');
  dbms_output.put_line( 'v_js_arr is : ' || v_js_arr.to_clob);
  end if;
  v_js_obj.put('media', v_js_arr);
  dbms_output.put_line('v_js_obj after put: ' || v_js_obj.to_clob);
  end loop;

Sample output is:
v_js_obj before put: {}
v_js_arr is : [1,2,3]
v_js_obj after put: {"media":[1,2,3]}
v_js_obj before put: {}
v_js_arr is null
v_js_obj after put: {"media":[1,2,3]} <------------------------------------

v_js_arr object is not cleared on assigning null value it seems, and during PUT() we get content from previous step


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.