Archive

Archive for the ‘PL/SQL’ Category

ExcelTable 1.3 : support for password-encrypted files

June 5, 2017 1 comment

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 :
XUTL_CDF
XUTL_OFFCRYPTO

 

Source code available on GitHub :

/mbleron/oracle/ExcelTable

 

A few words about the internals are following…

 

Read more…

(PL/SQL) CFBF File Reader

May 21, 2017 Leave a comment

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 :

/mbleron/oracle/CDFReader

 

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 :

{
  "key":"ABC",
  "arr":[1,2,3,null]
}

This is the output we’re looking for :

 ID TYPE    PARENT_ID NAME     POS      VALUE
--- ------ ---------- ------- ---- -----------
  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: , , ,

TreeBuilder – a PL/SQL graphical tree generator

February 26, 2017 Leave a comment

TreeBuilder computes the set of node coordinates necessary to represent a single-rooted tree in a graphical environment.
Node positioning is implemented using the improved version of Walker’s algorithm, published by Buchheim, J√ľnger and Leipert :
http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.16.8757

Tree data is exposed as a pipelined function.
The API also provides a constructor to visualize the tree as an SVG object.

Source code available on GitHub :

/mbleron/oracle/TreeBuilder

 

Read more…

How To : Convert Path Enumeration to Adjency List

October 4, 2016 Leave a comment

An Adjency List is a way to describe a finite graph structure by associating each vertex of the graph to each of its neighbours.
For a simple oriented tree, a possible implemention of the Adjency List model is to store a node along with its parent node reference.
In a RDBMS, this is indeed a very common way of storing hierarchical data, such as the Employee-Manager relationship :

SQL> select empno, ename, mgr
  2  from scott.emp;
 
EMPNO ENAME        MGR
----- ---------- -----
 7369 SMITH       7902
 7499 ALLEN       7698
 7521 WARD        7698
 7566 JONES       7839
 7654 MARTIN      7698
 7698 BLAKE       7839
 7782 CLARK       7839
 7788 SCOTT       7566
 7839 KING       
 7844 TURNER      7698
 7876 ADAMS       7788
 7900 JAMES       7698
 7902 FORD        7566
 7934 MILLER      7782

Such a structure can be easily traversed using a CONNECT-BY query, or Recursive Subquery Factoring :

SQL> select ename, ltrim(sys_connect_by_path(ename, '/'), '/') as path
  2  from scott.emp
  3  connect by prior empno = mgr
  4  start with mgr is null
  5  order siblings by empno;
 
ENAME      PATH
---------- -----------------------------------------------------------
KING       KING
JONES      KING/JONES
SCOTT      KING/JONES/SCOTT
ADAMS      KING/JONES/SCOTT/ADAMS
FORD       KING/JONES/FORD
SMITH      KING/JONES/FORD/SMITH
BLAKE      KING/BLAKE
ALLEN      KING/BLAKE/ALLEN
WARD       KING/BLAKE/WARD
MARTIN     KING/BLAKE/MARTIN
TURNER     KING/BLAKE/TURNER
JAMES      KING/BLAKE/JAMES
CLARK      KING/CLARK
MILLER     KING/CLARK/MILLER
 
SQL> with tree_walker (empno, ename, path) as (
  2    select empno, ename, ename
  3    from scott.emp
  4    where mgr is null
  5    union all
  6    select e.empno, e.ename, w.path || '/' || e.ename
  7    from tree_walker w
  8         join scott.emp e on e.mgr = w.empno
  9  )
 10  search depth first by empno set emp_order
 11  select ename, path
 12  from tree_walker;
 
ENAME      PATH
---------- ---------------------------------------------
KING       KING
JONES      KING/JONES
SCOTT      KING/JONES/SCOTT
ADAMS      KING/JONES/SCOTT/ADAMS
FORD       KING/JONES/FORD
SMITH      KING/JONES/FORD/SMITH
BLAKE      KING/BLAKE
ALLEN      KING/BLAKE/ALLEN
WARD       KING/BLAKE/WARD
MARTIN     KING/BLAKE/MARTIN
TURNER     KING/BLAKE/TURNER
JAMES      KING/BLAKE/JAMES
CLARK      KING/CLARK
MILLER     KING/CLARK/MILLER
 

Now, what if we want to – given only a list of paths – generate back an adjency data set?

For example, from this list of leaf paths :

KING/BLAKE/ALLEN
KING/BLAKE/JAMES
KING/BLAKE/MARTIN
KING/BLAKE/TURNER
KING/BLAKE/WARD
KING/CLARK/MILLER
KING/JONES/FORD/SMITH
KING/JONES/SCOTT/ADAMS

to :

  ID NAME     PARENT_ID
---- -------- ---------
   1 KING     
   2 JONES            1
   3 SCOTT            2
   4 ADAMS            3
   5 FORD             2
   6 SMITH            5
   7 BLAKE            1
   8 ALLEN            7
   9 WARD             7
  10 MARTIN           7
  11 TURNER           7
  12 JAMES            7
  13 CLARK            1
  14 MILLER          13

This post presents a way to achieve that result, using a pipelined table function.

 
Read more…

PLCalc – a PL/SQL RPN calculator

August 7, 2016 2 comments

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