Close

Comparing 19c vs 21c JSON key lists

I found this functional surprise while responding to questions about my previous article. When you invoke the GET_KEYS method of JSON_OBJECT_T, if the collection is empty, 19c will return a NULL value; 21c will return a JSON_KEY_LIST collection of 0 elements.

19c

SQL> DECLARE
  2      v_version      VARCHAR2(20);
  3      v_compatible   VARCHAR2(20);
  4      v_keylist      json_key_list;
  5  BEGIN
  6      DBMS_UTILITY.db_version(v_version, v_compatible);
  7      DBMS_OUTPUT.put_line(v_version);
  8      DBMS_OUTPUT.put_line(v_compatible);
  9      v_keylist := json_object_t('{}').get_keys;
 10
 11      IF v_keylist IS NULL
 12      THEN
 13          DBMS_OUTPUT.put_line('Null json_key_list ');
 14      ELSE
 15          DBMS_OUTPUT.put_line(
 16              'json_key_list of ' || TO_CHAR(v_keylist.COUNT) || ' elements');
 17      END IF;
 18  END;
 19  /
19.0.0.0.0
19.0.0
Null json_key_list

21c

SQL> DECLARE
  2      v_version      VARCHAR2(20);
  3      v_compatible   VARCHAR2(20);
  4      v_keylist      json_key_list;
  5  BEGIN
  6      DBMS_UTILITY.db_version(v_version, v_compatible);
  7      DBMS_OUTPUT.put_line(v_version);
  8      DBMS_OUTPUT.put_line(v_compatible);
  9      v_keylist := json_object_t('{}').get_keys;
 10
 11      IF v_keylist IS NULL
 12      THEN
 13          DBMS_OUTPUT.put_line('Null json_key_list ');
 14      ELSE
 15          DBMS_OUTPUT.put_line(
 16              'json_key_list of ' || TO_CHAR(v_keylist.COUNT) || ' elements');
 17      END IF;
 18  END;
 19* /
21.0.0.0.0
21.0.0
json_key_list of 0 elements

I originally did my testing on 21c and 23c databases for my prior article, I had to go back and make an update to make it backward compatible for 19c and lower versions as well.