(PL)SQL - JSON

When converting XML to JSON using apex_json procedures/functions like write(), you may run into the problem that arrays with a single entry are incorrectly (or maybe just inconsistently) serialized.
This is explained clearly on this page: https://tapadoo.com/regular-problem-with-xml-to-json-converters/.
Info on the apex_json procedures used can be found at https://oracle-base.com/articles/misc/apex_json-package-generate-and-parse-json-documents-in-oracle#xml-to-json.

I haven't found a solution for this yet, but I thought I'd share my example:

declare
  cursor c1 is
    with x as (
      select '
<company>
  <team>
    <employee>
      <name>Joe</name>
      <surname>Bloggs</surname>
    </employee>
  </team>
</company>' as x1
, '
<company>
  <team>
    <employee>
      <name>Joe</name>
      <surname>Bloggs</surname>
    </employee>
    <employee>
      <name>Jane</name>
      <surname>Doe</surname>
    </employee>
  </team>
</company>' as x2
      from dual
    )
    select xmltype(x.x1) as x1_xmltype
    ,      xmltype(x.x2) as x2_xmltype
    from x
  ;
begin
  apex_json.initialize_clob_output;
  for r1 in c1 loop
    apex_json.write(r1.x1_xmltype);
    apex_json.write(r1.x2_xmltype);
    dbms_output.put_line(apex_json.get_clob_output);
  end loop;
  apex_json.free_output;
end;

 
declare  cursor c1 is    with x as (      select '<company>  <team>    <employee>      <name>Joe</name>      <surname>Bloggs</surname>    </employee>  </team></company>' as x1, '<company>  <team>    <employee>      <name>Joe</name>      <surname>Bloggs</surname>    </employee>    <employee>      <name>Jane</name>      <surname>Doe</surname>    </employee>  </team></company>' as x2      from dual    )    select xmltype(x.x1) as x1_xmltype    ,      xmltype(x.x2) as x2_xmltype    from x  ;begin  apex_json.initialize_clob_output;  for r1 in c1 loop    apex_json.write(r1.x1_xmltype);    apex_json.write(r1.x2_xmltype);    dbms_output.put_line(apex_json.get_clob_output);  end loop;  apex_json.free_output;end;

Gives this JSON output:

{"team":{"employee":{"name":"Joe","surname":"Bloggs"}}}
{"team":[{"name":"Joe","surname":"Bloggs"},{"name":"Jane","surname":"Doe"}]}

xxxxxxxxxx
 
{"team":{"employee":{"name":"Joe","surname":"Bloggs"}}}{"team":[{"name":"Joe","surname":"Bloggs"},{"name":"Jane","surname":"Doe"}]}

I would have expected (and preferred):

{"team":[{"name":"Joe","surname":"Bloggs"}]}
{"team":[{"name":"Joe","surname":"Bloggs"},{"name":"Jane","surname":"Doe"}]}

xxxxxxxxxx
 
{"team":[{"name":"Joe","surname":"Bloggs"}]}{"team":[{"name":"Joe","surname":"Bloggs"},{"name":"Jane","surname":"Doe"}]}

I ran into this when making a template for Apex Office Print, which uses JSON input and markup. The markup for e.g. a {#data_loop}...{/data_loop} tag will fail if the loop only contains one row because of the extra "employee" element (the row will not be shown because the template markup doesn't expect that extra element).

Some methods to prettify JSON:

From Oracle 12c (note that returning clob is only available from 12.2):

select json_query('{a:[1,2,3,4]}', '$' returning varchar2(4000) pretty) as pretty_varchar2
,      json_query('{a:[1,2,3,4]}', '$' returning clob           pretty) as pretty_clob
from dual

xxxxxxxxxx
 
select json_query('{a:[1,2,3,4]}', '$' returning varchar2(4000) pretty) as pretty_varchar2,      json_query('{a:[1,2,3,4]}', '$' returning clob           pretty) as pretty_clobfrom dual

From Oracle 19 and up:

select json_serialize('{a:[1,2,3,4]}' returning varchar2 pretty truncate error on error) as pretty_varchar2
,      json_serialize('{a:[1,2,3,4]}' returning clob     pretty                        ) as pretty_clob
from dual

xxxxxxxxxx
 
select json_serialize('{a:[1,2,3,4]}' returning varchar2 pretty truncate error on error) as pretty_varchar2,      json_serialize('{a:[1,2,3,4]}' returning clob     pretty                        ) as pretty_clobfrom dual

The apex_json.write(p_cursor) procedure does a not-so-clever number conversion sometimes.
Whenever it comes across a string that it thinks looks like a number, it will convert it into a number. However, not all numbers are recognized as such, so the result is inconsistent. And any formatting you applied is then lost of course.

I suspect the internal conversion to XML done by apex_json.write is the cause here. See the Oracle documentation on this: "This procedure writes an array with all rows that the cursor returns. Each row is a separate object. If the query contains object type, collection, or cursor columns, the procedure uses write(xmltype) to generate JSON. Otherwise, it uses DBMS_SQL to fetch rows and the write() procedures for the appropriate column data types for output."

A way to fix this is to add a prefix/suffix to the number, and then later remove that - see the "k_spacer replace" in the function fn_apex_json_write_cursor in the code below.

[
  {
    "D_TEST" :
    [
      {
        "D_INPUT" : "Input: 234.00",
        "D_OUTPUT" : 234
      },
      {
        "D_INPUT" : "Input: 234.50",
        "D_OUTPUT" : 234.5
      },
      {
        "D_INPUT" : "Input: 234.56",
        "D_OUTPUT" : 234.56
      },
      {
        "D_INPUT" : "Input: 1,234.00",
        "D_OUTPUT" : "1,234.00"
      },
      {
        "D_INPUT" : "Input: 1,234.50",
        "D_OUTPUT" : "1,234.50"
      },
      {
        "D_INPUT" : "Input: 1,234.56",
        "D_OUTPUT" : "1,234.56"
      },
      {
        "D_INPUT" : "Input: 234.00",
        "D_OUTPUT" : "234.00"
      }
    ]
  }
]

xxxxxxxxxx
 
[  {    "D_TEST" :    [      {        "D_INPUT" : "Input: 234.00",        "D_OUTPUT" : 234      },      {        "D_INPUT" : "Input: 234.50",        "D_OUTPUT" : 234.5      },      {        "D_INPUT" : "Input: 234.56",        "D_OUTPUT" : 234.56      },      {        "D_INPUT" : "Input: 1,234.00",        "D_OUTPUT" : "1,234.00"      },      {        "D_INPUT" : "Input: 1,234.50",        "D_OUTPUT" : "1,234.50"      },      {        "D_INPUT" : "Input: 1,234.56",        "D_OUTPUT" : "1,234.56"      },      {        "D_INPUT" : "Input: 234.00",        "D_OUTPUT" : "234.00"      }    ]  }]

On database 18c, when executing the code below you will get a PLS-00801 internal error:

declare
  l_clob clob;
begin
  l_clob := json_object('key_1' value coalesce('a', 'b'));
end;

/*
Error:
  ORA-06550: line 0, column 0:
  PLS-00801: internal error [ph2exp:coalesce]
  ORA-06550: line 4, column 3:
  PL/SQL: Statement ignored
*/

​x
 
declare  l_clob clob;begin  l_clob := json_object('key_1' value coalesce('a', 'b'));end;​/*Error:  ORA-06550: line 0, column 0:  PLS-00801: internal error [ph2exp:coalesce]  ORA-06550: line 4, column 3:  PL/SQL: Statement ignored*/

On database 19c and up, this works OK.
A simple fix for 18c is to use nvl instead of coalesce.

Turns out that the path in json_table is case-sensitive - I had no idea...

For example:

select * from json_table ( some_clob, '$[*]' columns name varchar2(512 char) path Name )

xxxxxxxxxx
 
select * from json_table ( some_clob, '$[*]' columns name varchar2(512 char) path Name )

is not the same as

select * from json_table ( some_clob, '$[*]' columns name varchar2(512 char) path name )

xxxxxxxxxx
 
select * from json_table ( some_clob, '$[*]' columns name varchar2(512 char) path name )

Personally I always use the full syntax with quotes, like this:

select * from json_table ( some_clob, '$[*]' columns name varchar2(512 char) path '$.Name' )

xxxxxxxxxx
 
select * from json_table ( some_clob, '$[*]' columns name varchar2(512 char) path '$.Name' )

So I never noticed this until I reformatted some old code using the Beautifier in PL/SQL Developer.
For some reason (I would call it a bug) that Beautifier changes "path Name" into "path name".
Which creates a sneaky little bug in the code, which you may not notice until much later...

More info on the path syntax at https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/function-JSON_TABLE.html#GUID-0172660F-CE29-4765-BF2C-C405BDE8369A.

Dates and Timestamps IR

DT_STR (string)	DT_DT (date)	DT_TS (timestamp)	DT_TS_CAST_DATE (date)	DT_TS_TO_CHAR (string)
15-JUL-2019
2019-07-15	2019-07-15 00:00:00	15-JUL-19 12.00.00.000000 AM	2019-07-15 00:00:00	2019-07-15T00:00:00
2019-07-15 02:11:27 PM
2019-07-15 14:11:27
2019-07-15T14:11:27	2019-07-15 00:00:00	15-JUL-19 02.11.27.000000 PM	2019-07-15 14:11:27	2019-07-15T14:11:27
2019-07-15T14:11:27+00:00	2019-07-15 00:00:00	15-JUL-19 02.11.27.000000 PM	2019-07-15 14:11:27	2019-07-15T14:11:27
2019-07-15T14:11:27Z	2019-07-15 00:00:00	15-JUL-19 02.11.27.000000 PM	2019-07-15 14:11:27	2019-07-15T14:11:27
2025-02-24T10:52:18.123456	2025-02-24 00:00:00	24-FEB-25 10.52.18.123456 AM	2025-02-24 10:52:18	2025-02-24T10:52:18

Some ways to extract dates and timestamps from JSON.
I got the main example from Ask Tom but added a "cast to date" column and timestamp handling.

Page

Identification
	Page ID	1127 xxxxxxxxxx 1127
	Name	(PL)SQL - JSON xxxxxxxxxx (PL)SQL - JSON

Region

Identification
	Sequence	130 xxxxxxxxxx 130
	Title	apex_json.write (cursor) xxxxxxxxxx apex_json.write (cursor)
	Type	Static Content xxxxxxxxxx Static Content
Source
	Source Type	Static Content xxxxxxxxxx Static Content
	Region Source	<textarea class="textarea codemirror-json">&P1127_D_TEST.</textarea> xxxxxxxxxx <textarea class="textarea codemirror-json">&P1127_D_TEST.</textarea>

Item

Identification
	Name	P1127_D_TEST xxxxxxxxxx P1127_D_TEST
	Display As	Hidden xxxxxxxxxx Hidden
Advanced
Advanced	Warn on Unsaved Changes	Page Default xxxxxxxxxx Page Default
Source
	Source Used	Only when current value in session state is null xxxxxxxxxx Only when current value in session state is null
	Source Type	Function Body xxxxxxxxxx Function Body
	Source value or expression	return ted_p1127.fn_apex_json_write_cursor; xxxxxxxxxx return ted_p1127.fn_apex_json_write_cursor;

Package

Package
	Name	TED_P1127 xxxxxxxxxx TED_P1127
	Source	package ted_p1127 is function fn_apex_json_write_cursor return varchar2; end ted_p1127; xxxxxxxxxx package ted_p1127 is function fn_apex_json_write_cursor return varchar2; end ted_p1127;

Package Body

Package Body
	Name	TED_P1127 xxxxxxxxxx TED_P1127
	Source	package body ted_p1127 is k_spacer constant varchar2(10) := '#!SPACE!#'; function fn_apex_json_write_cursor return varchar2 is l_result varchar2(32767); l_cursor sys_refcursor; begin open l_cursor for select cursor ( with iv_d as ( -- NOK because there is a 0 at the end of the formatted number: select 1 as sort_order, to_char(234 ,'fm999G999D90') as d from dual union all select 2 as sort_order, to_char(234.5 ,'fm999G999D90') as d from dual union all -- OK because there is no 0 at the end of the formatted number: select 3 as sort_order, to_char(234.56 ,'fm999G999D90') as d from dual union all -- OK because this is not converted to a number: select 4 as sort_order, to_char(1234 ,'fm999G999D90') as d from dual union all select 5 as sort_order, to_char(1234.5 ,'fm999G999D90') as d from dual union all select 6 as sort_order, to_char(1234.56,'fm999G999D90') as d from dual union all -- OK because of the k_spacer replace later on: select 7 as sort_order, to_char(234 ,'fm999G999D90') \|\| k_spacer as d from dual ) select 'Input: ' \|\| d as d_input , d as d_output from iv_d order by iv_d.sort_order ) as d_test from dual ; apex_json.initialize_clob_output; apex_json.write(p_cursor => l_cursor); apex_json.close_all; l_result := json_query(apex_json.get_clob_output, '$' returning varchar2 pretty); l_result := replace(l_result, k_spacer, null); return l_result; end fn_apex_json_write_cursor; end ted_p1127; xxxxxxxxxx package body ted_p1127 is k_spacer constant varchar2(10) := '#!SPACE!#'; function fn_apex_json_write_cursor return varchar2 is l_result varchar2(32767); l_cursor sys_refcursor; begin open l_cursor for select cursor ( with iv_d as ( -- NOK because there is a 0 at the end of the formatted number: select 1 as sort_order, to_char(234 ,'fm999G999D90') as d from dual union all select 2 as sort_order, to_char(234.5 ,'fm999G999D90') as d from dual union all -- OK because there is no 0 at the end of the formatted number: select 3 as sort_order, to_char(234.56 ,'fm999G999D90') as d from dual union all -- OK because this is not converted to a number: select 4 as sort_order, to_char(1234 ,'fm999G999D90') as d from dual union all select 5 as sort_order, to_char(1234.5 ,'fm999G999D90') as d from dual union all select 6 as sort_order, to_char(1234.56,'fm999G999D90') as d from dual union all -- OK because of the k_spacer replace later on: select 7 as sort_order, to_char(234 ,'fm999G999D90') \|\| k_spacer as d from dual ) select 'Input: ' \|\| d as d_input , d as d_output from iv_d order by iv_d.sort_order ) as d_test from dual ; apex_json.initialize_clob_output; apex_json.write(p_cursor => l_cursor); apex_json.close_all; l_result := json_query(apex_json.get_clob_output, '$' returning varchar2 pretty); l_result := replace(l_result, k_spacer, null); return l_result; end fn_apex_json_write_cursor; end ted_p1127;

Region

Identification
	Sequence	170 xxxxxxxxxx 170
	Title	Dates and Timestamps IR xxxxxxxxxx Dates and Timestamps IR
	Type	Interactive Report xxxxxxxxxx Interactive Report
Source
	Source Type	Interactive Report xxxxxxxxxx Interactive Report
	Region Source	with rws as ( select '{ "dates" : [ "15-JUL-2019" ,"2019-07-15" ,"2019-07-15 02:11:27 PM" ,"2019-07-15 14:11:27" ,"2019-07-15T14:11:27" ,"2019-07-15T14:11:27+00:00" ,"2019-07-15T14:11:27Z" ,"2025-02-24T10:52:18.123456" ] }' as doc from dual ) select j.* , cast(j.dt_ts as date) as dt_ts_cast_date , to_char(j.dt_ts, 'YYYY-MM-DD"T"HH24:MI:SS') as dt_ts_to_char from rws , json_table ( rws.doc , '$' columns nested path '$.dates[]' columns ( dt_str varchar2 path '$' , dt_dt date path '$' null on error , dt_ts timestamp path '$' null on error ) ) j order by j.dt_str xxxxxxxxxx with rws as ( select '{ "dates" : [ "15-JUL-2019" ,"2019-07-15" ,"2019-07-15 02:11:27 PM" ,"2019-07-15 14:11:27" ,"2019-07-15T14:11:27" ,"2019-07-15T14:11:27+00:00" ,"2019-07-15T14:11:27Z" ,"2025-02-24T10:52:18.123456" ] }' as doc from dual ) select j. , cast(j.dt_ts as date) as dt_ts_cast_date , to_char(j.dt_ts, 'YYYY-MM-DD"T"HH24:MI:SS') as dt_ts_to_char from rws , json_table ( rws.doc , '$' columns nested path '$.dates[*]' columns ( dt_str varchar2 path '$' , dt_dt date path '$' null on error , dt_ts timestamp path '$' null on error ) ) j order by j.dt_str

Report Column

Column Definition
	Column Name	DT_STR xxxxxxxxxx DT_STR
	Display Type	ESCAPE_SC (Escape special characters) xxxxxxxxxx ESCAPE_SC (Escape special characters)
	Column Heading	DT_STR (string) xxxxxxxxxx DT_STR (string)

Report Column

Column Definition
	Column Name	DT_DT xxxxxxxxxx DT_DT
	Display Type	ESCAPE_SC (Escape special characters) xxxxxxxxxx ESCAPE_SC (Escape special characters)
	Column Heading	DT_DT (date) xxxxxxxxxx DT_DT (date)
Appearance
Appearance	Format Mask	YYYY-MM-DD HH24:MI:SS xxxxxxxxxx YYYY-MM-DD HH24:MI:SS

Report Column

Column Definition
	Column Name	DT_TS xxxxxxxxxx DT_TS
	Display Type	ESCAPE_SC (Escape special characters) xxxxxxxxxx ESCAPE_SC (Escape special characters)
	Column Heading	DT_TS (timestamp) xxxxxxxxxx DT_TS (timestamp)

Report Column

Column Definition
	Column Name	DT_TS_CAST_DATE xxxxxxxxxx DT_TS_CAST_DATE
	Display Type	ESCAPE_SC (Escape special characters) xxxxxxxxxx ESCAPE_SC (Escape special characters)
	Column Heading	DT_TS_CAST_DATE (date) xxxxxxxxxx DT_TS_CAST_DATE (date)
Appearance
Appearance	Format Mask	YYYY-MM-DD HH24:MI:SS xxxxxxxxxx YYYY-MM-DD HH24:MI:SS

Report Column

Column Definition
	Column Name	DT_TS_TO_CHAR xxxxxxxxxx DT_TS_TO_CHAR
	Display Type	ESCAPE_SC (Escape special characters) xxxxxxxxxx ESCAPE_SC (Escape special characters)
	Column Heading	DT_TS_TO_CHAR (string) xxxxxxxxxx DT_TS_TO_CHAR (string)

XML to JSON

Prettyfier

apex_json.write (cursor)

PLS-00801 with json_object and coalesce

json_table case-sensitive path

Dates and Timestamps

Dates and Timestamps IR

Code

Page

Region

Item

Package

Package Body

Region

Report Column

Report Column

Report Column

Report Column

Report Column

Column Actions