PLCalc – a PL/SQL RPN calculator

August 7, 2016 Leave a comment

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 :

  1. Extended expression syntax
  2. Support for variable-args functions
  3. Compilation to binary format
  4. Serialization to Presentation MathML format

Read more in the User Guide.

 

Available for download on GitHub :

/mbleron/oracle/PLCalc

 

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

How To : extract LONG column as CLOB using DBUriType

July 20, 2016 Leave a comment

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.

 

Read more…

Oracle SQL – Reading an Excel File (xlsx) as an External Table

June 21, 2016 1 comment

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 :

/mbleron/oracle/ExcelTable

Read more…

XML Namespaces 101

June 7, 2016 1 comment

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 : xmlns:prefix="my-namespace-1"

    The scope is the element where it appears and all its descendant elements and attributes, unless it is redefined using another declaration (e.g. xmlns:prefix="my-namespace-2").
    A binding declaration applies to all qualified (i.e. prefixed) in-scope elements and attributes.

  • default namespace declaration : xmlns="my-default-ns"

    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 (xmlns="").
    A default namespace declaration applies to all unqualified in-scope elements, but it does not apply to attributes.

Let’s consider a simple example :
Read more…

How To : using OUTER JOIN with XMLTABLE or XQuery

May 20, 2016 2 comments

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                    

Read more…

Categories: HowTo, XML DB, XQuery

Simple CSV parsing using XMLTABLE or JSON_TABLE

April 22, 2016 Leave a comment

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.

Read more…

Categories: JSON, SQL, XML DB Tags: , ,

How To : Load XML from File with Encoding Detection

April 10, 2016 1 comment

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 (csid argument).
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.

Read more…