PL/SQL RPN calculator

Some non-XML stuff for a change :)
This is something I’ve developed recently to evaluate stored expressions (formulas) using variables.
The “calculator” is written in PL/SQL and implements an RPN evaluation technique, as well as a method to convert infix expressions to RPN using the shunting-yard algorithm.
The evaluation function does not involve any dynamic SQL.

Program and additional objects available here : rpn_util.zip

 

XML Flattening revisited : Java-based pipelined function

As a follow-up to How To : Flatten out an XML Hierarchical Structure, here’s a fourth approach using a pipelined function built over the Java InfosetReader interface.
The function only works on an XMLType column/table stored as binary XML. Since we directly decode the binary stream and pipe the rows to the SQL engine, this method is faster, much more scalable and less memory-intensive.

 

1. Set up

The ODCI set up is based on : Pipelined Table Functions Example: Java Implementation from the Data Cartridge Developer’s Guide.

We start by creating an object type and its collection. These are the structures that will be filled by the Java program at runtime and returned to the SQL engine via the pipelined function :

CREATE TYPE XMLEdgeTableRow AS OBJECT (
  node_id        integer
, node_name      varchar2(2000)
, node_type      varchar2(30)
, parent_node_id integer
, node_value     varchar2(4000)
, namespace_uri  varchar2(2000)
);

CREATE TYPE XMLEdgeTable AS TABLE OF XMLEdgeTableRow;

 

then the implementation type :

Création d’une étiquette DPE depuis Oracle

As the title announces, exceptionally, this article is written in French. It describes how to generate the french version of the Energy Performance Certificate (EPC) chart from Oracle database, both as a BLOB image (using Java) and SVG file (using SQL/XML functions).
Translation available upon request ;)

En France, selon la règlementation en vigueur, le Diagnostic de Performance Énergétique (DPE) inclut une représentation visuelle de la performance énergétique d’un bâtiment sous la forme d’une étiquette marquant sa consommation annuelle sur une échelle de valeurs.

J’ai retenu deux méthodes assez différentes pour créer cette représentation depuis Oracle :

• Une procédure stockée Java générant une image (format PNG)
• Une simple requête SQL utilisant les fonctions SQL/XML pour créer un fichier au format SVG

How To : Access database tables from XSLT context

In this post we’ll see how to access a database table (or view) in the context of an XSL transformation.

The key feature allowing that is the ability to query a table using a DBUri URI.
In SQL or PL/SQL, DBUris are managed via the DBUriType object and its methods (see DBUris: Pointers to Database Data), and over HTTP protocol via the DBUriServlet.
The URI address is an XPath-based string, just like the oradb scheme available in XQuery.

In XSLT, we can pass a DBUri URI to the document() function in order to retrieve data in XML format. There are however some restrictions.

How To : Update XML nodes with values from the same document

This post was inspired by a recent thread on OTN where I suggested a few methods to update one or multiple nodes with values from the same document.

Let’s consider this simple structure :

<workbook>
 <worksheet sheetName="MySheet" sheetId="1"/>
 <worksheet sheetName="MySheet" sheetId="2"/>
 <worksheet sheetName="MySheet" sheetId="3"/>
</workbook>

and that we want to update the sheetName of each worksheet using the value in sheetId.

How To : Load XML data into multiple tables

Here’s a short article presenting a common technique to load data from a single XML document into different tables, using a single INSERT statement.
A typical use case is an XML structure describing a master-detail relationship, such as the classic invoice or purchase order document (header + line items).

How To : Read and Write Office 2003 Excel XML Files

Office Open XML (OOX) has become the default format with the release of Office 2007, but back in the 2003′s days, Microsoft had already developed a format to store Excel workbooks as XML.
A comprehensive overview is available here :
Dive into SpreadsheetML (Part 1 of 2)
Dive into SpreadsheetML (Part 2 of 2)

Contrary to OOX where data and metadata are stored in a multipart archive, an Excel workbook file in SpreadsheetML 2003 format consists in a single XML instance, and therefore easily managed using built-in Oracle XML functions and XML DB features.
In this article, I’ll focus on how to create and read such files with the help of SQL/XML functions, XSLT and XQuery.