Search notes:

ORA-40587: invalid JSON type

The following select statement produces an ORA-40587: invalid JSON type error.

select json_object_t('{"num": 42, "txt": "hello world"}') from dual;

However, this PL/SQL block runs fine:

declare
   j json_object_t;
begin
   j := json_object_t('{"num": 42, "txt": "hello world"}');
end;
/

My assumption (!) is that this error is thrown in SQL because json_object_t is not a persistable type.

Oracle considers scalar values not valid JSON

Oracle considers scalar values to be invalid JSON, the following PL/SQL block throws error ORA-40573:

declare
   obj json_element_t;
begin
   obj := json_element_t.parse(42);
end;
/

These two blocks don't throw the error:

declare
   obj json_element_t;
begin
   obj := json_element_t.parse('{"num": 42}');
end;
/
 
declare
   obj json_element_t;
begin
   obj := json_element_t.parse('[1,2,3]');
end;
/

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1759435703, '216.73.216.42', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/errors/ORA-40587_invalid-JSON-type(82): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78

ORA-40587: invalid JSON type

Oracle considers scalar values not valid JSON

See also