Archive for April, 2017

JSON Flattening Part 1 – The JSONFlatten function

April 30, 2017 Leave a comment

This article presents an approach to flatten out a JSON structure into an “Edge” table.
That’s something I’ve been exploring multiple times in the past for XML data, see for example : Yet Another XML Flattening Technique.

I’ll apply the same technique used for XMLFlattenDOM function to JSON data, leveraging the new JSON PL/SQL types introduced in the 12.2 release : JSON_ELEMENT_T, JSON_OBJECT_T, JSON_ARRAY_T.
As those object types implement a DOM-like API (based on the internal JDOM_T object), it is quite easy to adapt my previous approach to JSON.

Given the following (very) simple JSON object :


This is the output we’re looking for :

--- ------ ---------- ------- ---- -----------
  1 object          0
  2 string          1 key               ABC
  3 array           1 arr
  4 number          3            1      1
  5 number          3            2      2
  6 number          3            3      3
  7 null            3            4


Read more…


Oracle 12.2 – TO_CLOB and TO_BLOB enhancements

April 17, 2017 Leave a comment

Loading an external file from an Oracle directory to a BLOB, or a CLOB (for plain text) has always been relatively easy.
DBMS_LOB API provides procedures to do so :

Oracle 12.2 makes it even more easy by extending TO_BLOB and TO_CLOB functions with the ability to directly convert a BFILE to the corresponding LOB pointer.
TO_CLOB is also overloaded to accept a BLOB input (and a charset ID).

Read more…

Categories: PL/SQL, SQL Tags: , , ,