JSON_TABLE Chaining

April 12, 2015 3 comments

Here’s the first article of the JSON category, focusing on JSON_TABLE for unnesting nested JSON arrays.

 

0- Setup

We’ll first create a table to hold some JSON documents :

create table tmp_json (
  id integer
, doc clob check (doc is json)
)
lob (doc) store as securefile (cache);

and some docs :

insert into tmp_json
values (1,
'{"id":1,
 "myArray":[
  {"attr1":1, "attr2":"string1", "attr3":[1,2,3]},
  {"attr1":2, "attr2":"string2", "attr3":null},
  {"attr1":3, "attr2":"string3", "attr3":[4,5]}
 ]}'
);

insert into tmp_json
values (2,
'{"id":2,
 "myArray":[
  {"attr1":1, "attr2":"string1", "attr3":[7,8]},
  {"attr1":2, "attr2":"string2", "attr3":null}
 ]}'
);

 

Read more…

Categories: JSON Tags: , ,

PL/SQL RPN calculator – version 2

November 24, 2014 2 comments

This post presents a new version of my PL/SQL RPN calculator.
It now includes :

  • a regex-free tokenizer
  • a recursive-descent parser to validate the input expression
  • an improved evaluator based on a compiled “type-aware” RPN expression

Program and additional objects available here : rpn_util_v2.zip

 

Read more…

Categories: Fun, PL/SQL Tags: ,

XMLTransform Tips and Tricks

November 16, 2014 3 comments

No real new stuff there but as I’m a big user of XSLT transformation inside the database, I just thought I could share a few interesting (hidden) things I’ve encountered so far, that is :

  1. Using XSLT parameters with XMLTransform
  2. Using text output method with XMLTransform and XMLType.transform method

 

Read more…

How To : Parse sibling repeating groups without wrapper element

November 9, 2014 Leave a comment

Long time no post, so here’s a new (short) article.
Behind the cryptic title lies a situation like this :

<root>
  <id>1</id>
  <name>SMITH</name>
  <id>2</id>
  <name>WILSON</name>
  <id>3</id>
  <name>GRANT</name>
</root>

where each logical record {id, name} is not enclosed by a row tag.
This is the result we want out of that data :

ID    NAME
----- ---------
    1 SMITH
    2 WILSON
    3 GRANT

 

Read more…

XMLTABLE vs. External XSLT preprocessor

September 16, 2013 2 comments

We all know – I hope ;) – about XMLTABLE function to shred an XML document into relational rows and columns. In this article, I will describe an alternative approach to achieve the same result with an external table and the preprocessor feature.
And in case you wonder, no, it’s not one of this attempt to implement an XML parser using SQL*Loader syntax, something we can see from time to time as an answer to “How can I load my XML file using SQL*Loader?”.
This method relies on the external table preprocessor to transform the input XML file into CSV format and pipe the result to the loader.

 

Read more…

New 12c XMLTABLE’s “RETURNING SEQUENCE BY REF” clause

September 9, 2013 Leave a comment

This is a short article about the new 12c RETURNING SEQUENCE BY REF feature available with XMLTable function.
This clause allows us to access the complete tree of nodes produced by the main XQuery expression. In particular, we can now use reverse axes in the COLUMNS PATH expression.
Below is an example using this method, compared with prior releases’ techniques to achieve the same result (all queries tested on 12.1.0.1).

 

Read more…

PL/SQL RPN calculator

February 3, 2013 3 comments

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

Update 2014-11-24 – upgraded version available here :
PL/SQL RPN calculator – version 2

 

Read more…