Oracle JSON ADT in Collection

jsonoracleoracle-18c

Why is the table() operator not working?

create or replace type tmp_011040_t2 as
  table of (JSON_OBJECT_T) NOT PERSISTABLE;

declare
  l_objects tmp_011040_t2;
begin
  l_objects := tmp_011040_t2();
  l_objects.extend(1);
  l_objects(l_objects.last) := JSON_OBJECT_T.parse('{"a":"1","b":"1"}');
  l_objects.extend(1);
  l_objects(l_objects.last) := JSON_OBJECT_T.parse('{"a":"2","b":"2"}');
  for i in (select * from table(l_objects) ) loop -- ERROR!!!
      null;
  end loop;
end;

-- ORA-40573: Invalid use of PL/SQL JSON object type.

Best Answer

$ oerr ora 40573
40573, 00000, "Invalid use of PL/SQL JSON object type."
// *Cause:  An attempt was made to use a PL/SQL JavaScript Object Notation
//          (JSON) object type in an invalid context.
// *Action: Use PL/SQL JSON object types only inside PL/SQL.

Example:

set serveroutput on
declare
  l_objects tmp_011040_t2;
  l_index pls_integer;
begin
  l_objects := tmp_011040_t2();
  l_objects.extend(1);
  l_objects(l_objects.last) := JSON_OBJECT_T.parse('{"a":"1","b":"1"}');
  l_objects.extend(1);
  l_objects(l_objects.last) := JSON_OBJECT_T.parse('{"a":"2","b":"2"}');
  l_index := l_objects.first;
  while (l_index is not null)
  loop
    dbms_output.put_line(l_objects(l_index).get_string('a'));
    l_index := l_objects.next(l_index);
  end loop;
end;
/

1
2


PL/SQL procedure successfully completed.
Related Question