As you might know, Oracle 11g introduced a new format to store and transfer XML content : Binary XML, also known as CSX (Compact Schema-aware XML), which provides optimization in both storage and processing areas.
Binary XML became the default XMLType storage method in version 220.127.116.11.
The purpose of this post is to present a short overview of the format, with a concrete example, as well as some Oracle specifics regarding Binary XML persistent storage in the database.
Although Binary XML has been designed with schema-awareness in mind, I’ll only focus on the non schema-based format.
PLCalc is a revamped version of my previous work : RPN_UTIL.
The existing code has been refactored and modularized, and now includes the following features :
- Extended expression syntax
- Support for variable-args functions
- Compilation to binary format
- Serialization to Presentation MathML format
Read more in the User Guide.
Available for download on GitHub :
Here’s a short post presenting an easy way to extract a LONG column as a CLOB.
Although the LONG data type was deprecated years ago in favour of CLOB, many Oracle dictionary views still use it.
Adrian Billington’s great article – working with long columns – shows different ways to access LONG columns in SQL, in particular when the size exceeds 32k. One of the solutions uses DBMS_SQL to fetch the LONG value piecewise into a CLOB.
What I’ll describe here is technically based on the same approach but only involves a single built-in function : SYS_DBURIGEN.
I’ve been thinking about it for quite a long time and never really had time to implement it, but it’s finally there : a pipelined table interface to read an Excel file (.xlsx) as if it were an external table.
It’s entirely implemented in PL/SQL using an object type (for the ODCI routines) and a package supporting the core functionalities.
Available for download on GitHub :
Back to basics with a focus on XML namespaces.
A lot of people still struggle to use and reference namespaces correctly in XML-related functions, and most often try random combinations until it works correctly.
Hopefully, this post will clear a few things up :)
1. What is a namespace?
A namespace is not some exotic object but just one out of the two parts that form a node name.
In the XML Object Model, the node name of an element or attribute is composed of :
- a namespace URI, i.e. the namespace name
- a local name
If the namespace uri is absent (null), the node is said to be in no namespace.
2. Default namespaces and prefixes
In an XML document or fragment, a namespace can be defined in two ways :
- namespace binding (prefix) declaration :
The scope is the element where it appears and all its descendant elements and attributes, unless it is redefined using another declaration (e.g.
A binding declaration applies to all qualified (i.e. prefixed) in-scope elements and attributes.
- default namespace declaration :
The scope is the element where it appears and all its descendants, unless it is redefined using another declaration (e.g.
xmlns="new-default-ns") or undefined using an empty declaration (
A default namespace declaration applies to all unqualified in-scope elements, but it does not apply to attributes.
Let’s consider a simple example :
Here is a short reminder post about how to use or implement outer join in XMLTABLE or XQuery.
The typical scenario for which we would need to outer join is the flattening of a master-detail hierarchical structure with some logical records missing in the detail part.
For instance, given this input XML document :
<root> <person id="1"> <name>John</name> <details> <phone type="mobile">111-11-2222-22</phone> <phone type="home">555-11-2222-22</phone> </details> </person> <person id="2"> <name>Jane</name> <details> <phone type="mobile">111-11-2222-33</phone> </details> </person> <person id="3"> <name>Sam</name> <details/> </person> </root>
We’d like to get the following result set :
PERSON_ID PERSON_NAME PHONE_TYPE PHONE_NUMBER ---------- ----------- ---------- --------------- 1 John mobile 111-11-2222-22 1 John home 555-11-2222-22 2 Jane mobile 111-11-2222-33 3 Sam
Oracle does not currently provide any built-in functionality to parse a flat file stored in the database as CLOB (or BLOB), like the External Table facility for external files.
Mike Kutz submitted the idea a couple of years ago : parse CLOBS/BLOBS using “external table” capability.
Parsing a simple CSV content, with no quoted field, is however possible with minimum effort using XMLTABLE or JSON_TABLE functions.
Both approaches are based on a little preprocessing to convert the data to the required format and pass it to the corresponding function.
The last part of this post will focus on the limitations.