How To : Generate Empty XML Attributes

February 17, 2018 Leave a comment

It is well-known that XML attributes may be generated using SQL/XML publishing function XMLAttributes().
It is also well-known that XMLAttributes() does not generate anything when passed an expression that evaluates to NULL :

If an attribute value expression evaluates to NULL, then no corresponding attribute is created.

So, how can we generate an empty attribute using SQL?

<root attr=""/>

Following are a compilation of “tricks” (more than official methods) to generate empty XML attributes, and empty namespace declarations in a SQL context.
I’ll divide them into two categories :

  • Document post-processing : target attributes we want to be empty are transformed after the complete document has been generated
  • In-place generation : empty attributes are directly generated in the same “flow”


Read more…

Categories: HowTo, SQL, XQuery Tags: , ,

ExcelTable 1.6 : support for cell comments

January 3, 2018 Leave a comment

Here’s the new version of ExcelTable, which can now extract cell comments as regular columns.
Not much change in the user interface, except an extended column syntax specification to declare a request for cell metadata instead of its value :

For example, using sample file ooxdata3.xlsx :

SQL> select t.*
  2  from table(
  3         ExcelTable.getRows(
  4           ExcelTable.getFile('TMP_DIR','ooxdata3.xlsx')
  5         , 'DataSource'
  6         , q'{
  7             "RN"             for ordinality
  8           , "SPARE2"         varchar2(30)   column 'F'
  9           , "SPARE2_COMMENT" varchar2(2000) column 'F' for metadata (comment)
 10           }'
 11         , '2:11'
 12         )
 13       ) t
 14  ;

--- ------ ------------------------------
  3 OK     bleronm:
           This is a comment.

  4 OK
  6 OK
  9        This is
           another comment
           on three lines


10 rows selected.


Source code available on GitHub :



A few words about the internals are following…


Read more…

Categories: Miscellaneous, PL/SQL, SQL, XQuery Tags:

How To : Nest Multiple JSON Path Expression Filters

July 9, 2017 Leave a comment

A short post about JSON Path Expressions.
I will focus on filter expressions (now officially documented as of Oracle 12.2) we can use in conjunction with JSON_EXISTS function, and more specifically how to nest multiple relative filter steps.

Here’s an example of what I mean :

      {"name":"x", "v":1},
      {"name":"y", "v":2},
      {"name":"z", "v":1.5}

and say we want to find documents (like the one above) where content.type = 1 and content.values contains an x item having v > 0.


Read more…

ExcelTable 1.3 : support for password-encrypted files

June 5, 2017 3 comments

Here’s the new version of ExcelTable, which can now read password-encrypted files.
It supports Standard and Agile encryption methods, as specified in [MS-OFFCRYPTO].

By default, Office 2007 will encrypt using the Standard method whereas Office 2010 and onwards use Agile encryption.
AES (128 or 256) is usually the default algorithm on standard Office installations.
Because latest Office versions (2013+) make use of SHA-2 hashing algorithms, Oracle 12c is required to read Excel documents encrypted in those versions.

Basically, the only change from ExcelTable 1.2 is the addition of an optional argument p_password in getRows() function :

function getRows (
  p_file     in blob
, p_sheet    in varchar2
, p_cols     in varchar2
, p_range    in varchar2 default null
, p_method   in binary_integer default DOM_READ
, p_password in varchar2 default null
return anydataset pipelined
using ExcelTableImpl;

The following dependencies are also required :


Source code available on GitHub :



A few words about the internals are following…


Read more…

(PL/SQL) CFBF File Reader

May 21, 2017 5 comments

In this post, I’ll be presenting a PL/SQL utility to read Compound File Binary Format (CFBF).
CFBF is originally a Microsoft proprietary format, which has now been published through the Open Specifications program.

In a nutshell, a CFBF file, or Compound Document File (CDF), is a container assembled like a FAT filesystem, which can be explored as a regular directory structure.

The purpose of the post is not to discuss about the format itself so I’ll let the reader browse the specs to get more familiar with it :

CFBF is the basis for various file formats used in Windows systems.
To cite a few, .MSI (Microsoft Windows Installer) and the well-known .DOC, .XLS, .PPT etc. from the Office 97-2003 Suite.

So, why am I interested in developing a CFBF PL/SQL reader?

  1. Because it’s fun
  2. Because password-encrypted Open Office XML files (.xlsx, .docx …) are stored in a CFBF container, and I thought it would be a good enhancement to my ExcelTable interface.

Source code available on GitHub :



Read more…

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: , , ,