Next Step in Row generation in Oracle Database 12c SQL using JSON_TABLE

0

One of those things SQL developers are frequently looking at is the generation of rows: having a query return records that do not really exist. For example to generate test data or to produce records for all days in a month. Tom Kyte usually selects from data dictionary views. Various tricks make the rounds, for example based on CONNECT BY or CUBE or UNPIVOT. This blog article by Natalka Roshak (2015) compares various row generation techniques.

In this article, I am demonstrating a new – as far as I know – way of generating records. The underlying mechanism I am using is the JSON_TABLE operator that was introduced in Oracle Database 12cR1 (12.1.0.1) in 2014. This operator can expose data from a JSON document in a relational way – similar to what TABLE does to collections and XMLTABLE does for XML documents.

If the input to JSON_TABLE is a JSON Array, then the operator will produce a record for each element in the array.

For example:

image

 

A JSON Array is a simple string: starting with a square bracket and ending with one, in between it contains elements separated by commas: [1,2]  or [‘a’,’b’’,’c’]. In order to generate a specific number of rows we need a JSON array with that specific number of elements. Since the JSON document is simply a string – we can use string operators to create the array. And we have in RPAD (and LPAD) a function that can build up a string according to specifications: rpad(‘1’,21,’,2’) will return a string that consists of the character 1 followed by the string ‘,2’ as many times as fits in 21 characters (10 times): 1,2,2,2,2,2,2,2,2,2,2.

The query to produce a JSON array with the desired number of elements is as simple as:

select rpad('1',-1+2*(:desired_number_of_elements),',2') from dual

Knowing that we can easily produce a JSON document with an array holding the desired number of elements and knowing that JSON_TABLE will turn that document into relational records on the fly, we can create the query to generate rows:

SELECT r.*
FROM   JSON_TABLE('['||rpad('1',-1+2*( <strong>42</strong>),',1')||']', '$[*]'
COLUMNS (
"rn" PATH '$'
)) r;

assuming 42 is the desired number of rows. Plug in your own favorite number of rows.

image

 

Generating a list of weekdays can now be implemented as follows:

SELECT to_char( rownum+ to_date('19-09-2016','DD-MM-YYYY'),'DAY') day
FROM   ( select rws.rn
from   JSON_TABLE('['||rpad('1',-1+2*(7),',1')||']', '$[*]'
COLUMNS (
"rn" PATH '$'
)
) rws
)  days

 

image

 

The generation of the JSON document can be pushed to an inline PL/SQL Function like this:

with
function json_array_desired_length
(p_desired_length number)
return varchar2
is
begin
return '['||rpad('1',-1+2*p_desired_length,',1')||']';
end;
select to_char( rownum+ to_date('19-09-2016','DD-MM-YYYY'),'DAY') day
from (  select rws.rn
from JSON_TABLE( json_array_desired_length(7), '$[*]'
COLUMNS (
"rn" PATH '$'
)) rws
) days

 

And this function could be a utility function in a global package as well obviously, rendering the query as follows:

select to_char( rownum+ to_date('19-09-2016','DD-MM-YYYY'),'DAY') day
from (  select rws.rn
from JSON_TABLE( util.json_array_desired_length(7), '$[*]'
COLUMNS (
"rn" PATH '$'
)) rws
) days

 

or more compactly:

 

select to_char( rownum+ to_date('19-09-2016','DD-MM-YYYY'),'DAY') day
from (  select rws.rn from JSON_TABLE( util.json_array_desired_length(7), '$[*]' COLUMNS ("rn" PATH '$' )) rws ) days

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.