Practical Oracle SQL: Mastering the Full Power of Oracle Database

© Kim Berg Hansen 2020

K. Berg HansenPractical Oracle SQLhttps://doi.org/10.1007/978-1-4842-5617-6_6

6. Iterative Calculations with Multidimensional Data

Kim Berg Hansen¹ 

(1)

Middelfart, Denmark

 

You won’t find a multitude of real-life examples using the model clause, apart from doing recursion and iteration as I showed in Chapter 4. Recursive subquery factoring came in version 11, but with the model clause, you could do recursion from version 10. However, the real power of the model clause is the way you can address data in multiple dimensions in an array-like fashion, building formulas similar to the way spreadsheets work.

A nested table type in Oracle has a single dimension (index), and the “cell” can be a scalar or a structured type. If you have ...