0

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.
  • What are you expecting select * from table() to do? Return a row per l_objects element, each with 2 columns? – Philᵀᴹ Nov 8 at 16:03
1
$ 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.