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