Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
I have JSON data in a CLOB and I want to pretty print it.
According to the documentation https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_SERIALIZE.html
The json_serialize function takes JSON data of any SQL data type ( VARCHAR2, CLOB, BLOB ) as input
But CLOB doesn't work, only the first and second of the following examples give a result
select json_serialize (
'{"a":1, "b": [{"b1":2}, {"b2": "z"}]}' returning varchar2 pretty
) pretty_js
from dual;
select json_serialize (
utl_raw.cast_to_raw('{"a":1, "b": [{"b1":2}, {"b2": "z"}]}') returning varchar2 pretty
) pretty_js
from dual;
select json_serialize (
CAST('{"a":1, "b": [{"b1":2}, {"b2": "z"}]}' AS CLOB) returning varchar2 pretty
) pretty_js
from dual;
If I specify RETUNING CLOB, then even the ‘working’ examples return a truncated result
select json_serialize (
utl_raw.cast_to_raw('{"a":1, "b": [{"b1":2}, {"b2": "z"}]}') returning varchar2 pretty
) pretty_js
from dual;
PRETTY_JS
----------------------
{
"a" : 1,
"b" :
[
{
"b1" : 2
},
{
"b2" : "z"
}
]
}
select json_serialize (
utl_raw.cast_to_raw('{"a":1, "b": [{"b1":2}, {"b2": "z"}]}') returning clob pretty
) pretty_clob
from dual;
PRETTY_CLOB
----------------------
{
"a" : 1,
"b" :
[
{
"b1" : 2
},
{
"b2" : "z"
Returning CLOB is truncatet to ~80 characters
I tried https://jeffkemponoracle.com/2021/04/pretty-print-json-in-pl-sql/
But I only get an empty CLOB.
Any idea what's wrong?