Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Pretty-Print JSON (CLOB)

MarwimMar 28 2024

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?

This post has been answered by Paulzip on Apr 2 2024
Jump to Answer
Comments
Post Details
Added on Mar 28 2024
13 comments
216 views