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 : Flatten out a Recursive XML Document

January 17, 2017 Leave a comment

I blogged a few times about XML flattening in the past :

Those posts were about converting any XML structure and data into a schema-less model called “Edge”.
What I’ll be presenting here applies to XML documents having a recursive structure,
e.g.

<emp id="1">
  <emp id="2"/>
  <emp id="3">
    <emp id="4"/>
    <emp id="5"/>
  </emp>
</emp>

which could be converted to an adjency list model :

ID  PARENT_ID
--- ---------
  1
  2         1
  3         1
  4         3
  5         3

or, denormalized into level-based columns, like this :

ID_1 ID_2 ID_3
---- ---- ----
   1    2
   1    3    4
   1    3    5

 

Read more…

How To : Access Oracle Sequence from XQuery

December 11, 2016 1 comment

In this new post, we’ll see how to access an Oracle sequence from an XQuery expression.
I already wrote about a similar topic a few years ago, in How To : Access database tables from XSLT context.
Pretty much the same concepts presented back then for XSLT also apply here with XQuery.

The solution relies on the ability to query relational or object view data via the oradb URI scheme.
By wrapping the sequence call in a function and a view, we can fetch the sequence value using fn:collection() function.

In the following developments, I’ll distinguish two cases :

  • fetching sequence values from a statically declared sequence
  • fetching values from different sequences dynamically

Preliminary setup

Let’s first create a sequence :

create sequence my_seq;

And the function.
It takes the sequence name as a parameter and use native dynamic SQL to retrieve the value :

create or replace function get_seq_value (
  p_sequence_name in varchar2
) 
return number
is
  l_result  number;
begin
  execute immediate 'SELECT '||p_sequence_name||'.NEXTVAL FROM DUAL' into l_result;
  return l_result;
end;
/

 

Read more…

ExcelTable 1.2 : introducing streaming support for large files

November 15, 2016 1 comment

ExcelTable is my attempt at building a SQL query interface to read MS Excel files in xlsx (or xlsm) format.
Version 1.2 is now available with the following new features :

  • Streaming read support for large Excel files
  • setFetchSize() routine to limit the number of rows processed per request

 
/mbleron/oracle/ExcelTable

 

Streaming read method

The getRows() function has been extended with an additional optional argument p_method.
Allowed values are as follows :

-- Read methods  
DOM_READ     constant binary_integer := 0;
STREAM_READ  constant binary_integer := 1;

The default is 0 (DOM_READ).

The streaming method requires Java (StAX API) and is much more scalable than the DOM_READ method when accessing large files.
Please see the README for more details about what to install depending on the target database version.

Example on a 500,000-row file (bigfile.xlsx) :

select * 
from table(
       ExcelTable.getRows(
         ExcelTable.getFile('TMP_DIR','bigfile.xlsx')
       , 'data'      
       , q'{ 
            "ID"           number
          , "FIRST_NAME"   varchar2(15)
          , "LAST_NAME"    varchar2(20)
          , "EMAIL"        varchar2(30)
          , "GENDER"       varchar2(10)
          , "IP_ADDRESS"   varchar2(16)
          , "COMMENT"      varchar2(4000)
          , "IMAGE"        varchar2(4000)
          , "DT"           date  format 'DD/MM/YYYY'

         }'
       , 'A2'
       , 1
       )
     ) t ;

 

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…

Inside Binary XML

August 27, 2016 Leave a comment

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 11.2.0.2.

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.

 

Read more…