Skip to Main Content
Ted Struik - Oracle
Home
Ted Struik - Oracle
Search
Site Map
Statistics
Page Errors
Apex
General
Autocomplete
Conditions
Errors
Items used in PL/SQL
Validations
Dynamic Actions
Logs
Version
App-wide Items
Error Handling
Apex Views
Apex Upgrade 4.2 to 5.1
Database Dependencies
Show Source Code
URLs
Authorization
Modal Dialogs
Apex Office Print (AOP)
Apex-Sert
Reports
Checkboxes
Download
Errors
Nowrap Columns
Substitution Strings
Use IR results in pl/sql
Clickable Row
Row Highlight
Translate Filters etc.
Bar Charts
Column Usage
Reset Pagination
Hide IR Actions menu items
Get Classic Report query
Interactive Grid
ORA-20987
ERR-1002
Copying Text
IG Info
Images & Fonts
Standard Icons & Fonts
WebDav
Font Awesome
Icons/image popup LOV
Lists
Hierarchy
LOVs
Definition Bug
Set 1-item LOV to Disabled
With Clause Errors
Modal LOV Plugin
HTML / CSS / JavaScript
Vertical Align
Legend CSS
Geolocation
Apex.confirm
Pie Charts
Codemirror
Theme Roller
Catching Key Strokes
Browser Cache
Locale
RequireJS
Collapsible Regions
Import / Export
Dynamic Translations
Translated Pages
APEXExport (java)
apex_export (pl/sql)
Component Settings
Charts
Translate Charts
[Reports] Bar Charts
Shuttles
Shuttle Sorting
Disable Shuttle Items
Menus
Accordion-Like
Scroll bug
Authorization
Session State
About Session State
Rollback & Commit
Performance of v()
Apex Clone Session
Request
Collections
ORA-20101 & ORA-20104
ORA-00001
Max Row Count
(PL)SQL
External Tables
SQL Plus
Conditions
Long
ORDImage
Date & Timestamp
CGI environment
Sys_context
Rounding percentages
Apex_string.split in SQL
Dbms_metadata
Instr
Regular Expressions
Rollback to Savepoint
Use table aliases
(Pipelined) Bulk Collect
JSON
Source Code Search (PL/SQL)
Source Code Search (SQL)
Format Models
Pipelined Table Functions
Hint no_unnest
Listagg
DBA
Backup & Restore
Users
Export & Import
DBMS Jobs
Tablespaces
Table Size
Flush cache
Access Control List (ACL)
Locks
Java
Constraints
Object Dependencies
Package Dependencies 1
Package Dependencies 2
Grants
SQL History
Startup Errors
ORDS
Miscellaneous
Holidays
E-mail
NLExtract
Windows
VPN
VMware
Links
Google
Visualization Orgchart
Hide Search Results
Reverse Proxy
To-do List
Cytoscape
Graphics
Upscaling
Amazon Prices
Domoticz
DDoS
Hobbies
Motorcycles
'95 Kawasaki ZZR 600
'94 Kawasaki ZZR 1100
'91 Honda CBR 600 F2
'95 Kawasaki ZZR 1100
'99 Honda CBR 1100 XX
'00 Honda CBR 1100 XX
'02 Kawasaki ZX-12R (NL)
'05 Ducati 999 (NL)
'08 Honda Fireblade
'07 Suzuki Bandit 1250A
'18 Suzuki GSX-S 1000 F
MotoGP
Garfield
Lyrics
Skydive
Woodworking
Pantorouter
16 inch Bandsaw
Introduction
Frame
Wheels
Wheel mounts
Blade guides
Trunions and table
Enclosure
Alignment and more
Box Joint Jig
Foldable Sawhorses
Strip Sander XL
Photography
Sony RX10 IV
Search For
XML to 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 '
Joe
Bloggs
' as x1 , '
Joe
Bloggs
Jane
Doe
' 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"}]}
I would have expected (and preferred):
{"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).
Prettyfier
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
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
apex_json.write (cursor)
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" } ] } ]
PLS-00801 with json_object and coalesce
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 */
On database 19c and up, this works OK.
A simple fix for 18c is to use nvl instead of coalesce.
Code
Page
Name
Page ID
1127
Name
(PL)SQL - JSON
Region
Identification
Sequence
130
Title
apex_json.write (cursor)
Type
HTML/Text
Source
Source Type
STATIC_TEXT
Region Source
<textarea class="textarea codemirror-json">&P1127_D_TEST.</textarea>
Item
Identification
Sequence
10
Name
P1127_D_TEST
Display As
Hidden
User Interface
Region
apex_json.write (cursor)
Source
Source Used
Only when current value in session state is null
Source Type
Function Body
Source value or expression
return ted_p1127.fn_apex_json_write_cursor;
Package
Package
Name
TED_P1127
Source
package ted_p1127 is function fn_apex_json_write_cursor return varchar2; end ted_p1127;
Package Body
Package Body
Name
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;