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.
This post was inspired by this recent OTN thread : LPX-00200: could not convert from encoding UTF-8 to UCS2, where it was asked if it were possible to load an XML file without actually knowing its character encoding.
We can already build an XMLType instance from a BFILE, using the corresponding constructor :
constructor function XMLType( xmlData IN bfile , csid IN number , schema IN varchar2 := NULL , validated IN number := 0 , wellformed IN number := 0 ) return self as result deterministic parallel_enable
However, the character encoding must be known upfront and passed to the constructor (
For example :
SQL> select XMLSerialize(document 2 xmltype( 3 bfilename('TMP_DIR','test_utf-8_bom.xml') 4 , nls_charset_id('AL32UTF8') 5 ) 6 ) as xmldoc 7 from dual ; XMLDOC -------------------------------------------------------------------------------- <?xml version="1.0" encoding="UTF-8"?> <root>Contrôle:€</root>
Following is a little helper function that provides basic character encoding detection for XML files.
It should work for most 8-bit encodings (except EBCDIC), as well as UTF-16 (little and big-endian) and of course UTF-8.
The function uses this algorithm to detect the encoding :
- If a byte order mark (BOM) is present then UTF-8, UTF-16LE or UTF-16BE is assumed, respectively :
- 0xEFBBBF = UTF-8
- 0xFFFE = UTF-16LE
- 0xFEFF = UTF-16BE
- If no BOM is found then it looks for an XML prolog :
- If no prolog or encoding declaration is found then a default encoding is assumed.
- Else, the specified encoding is used.