Oracle 23c: Table Value Constructor (EN)

Let’s say we need a way to convert bytes into something human readable, like kB, MB and so on. For this purpose we need a constant table, but we do not want to create a persistant one.

Before Oracle 23c, when it came to creating „on the fly“ data or the need for an instant table within Oracle SQL, you had the following options. Create this data by using SELECTS concatenated by UNION ALLs:

WITH unit_table AS (
   SELECT 1 AS id, 'B' AS unit, 0 AS lo, 899 AS hi FROM dual
   UNION ALL
   SELECT 2, 'kB', 900, 9e5 - 1 FROM dual
   UNION ALL
   SELECT 3, 'MB', 9e5, 9e8 - 1 FROM dual
   UNION ALL
   SELECT 4, 'GB', 9e8, 9e100 FROM dual
   )
SELECT f.file_id, ROUND (f.bytes / POWER (1024, u.id - 1), 2) AS f_size, u.unit
   FROM dba_data_files f
   JOIN unit_table u
     ON f.bytes BETWEEN u.lo AND u.hi
  ORDER BY f.file_id;

So this will do the trick, but the WITH clause looks clumsy. In Oracle there is still another way. The very underrrated CONNECT BY clause can not only be used for hierarchical queries but – since it is just a loop – also for iterations:

WITH unit_table AS (
   SELECT ROWNUM AS id,
          REGEXP_SUBSTR ('B kB MB GB', '\S+', 1, ROWNUM) AS unit,
          TRUNC (9*POWER (10, 3*(ROWNUM - 1) - 1)) AS lo,
          CASE ROWNUM WHEN 1 THEN 900 WHEN 2 THEN 9e5 WHEN 3 THEN 9e8 ELSE 9e100 END AS hi 
      FROM dual
      CONNECT BY rownum <= 4
   )
SELECT f.file_id, ROUND (f.bytes / POWER (1024, u.id - 1), 2) AS f_size, u.unit
   FROM dba_data_files f
   JOIN unit_table u
     ON f.bytes BETWEEN u.lo AND u.hi
  ORDER BY f.file_id;

Even harder to read, but extra points for ingenuity 🙂

Coming with Oracle 23c there is finally a better method available:

The table value constructor!

WITH unit_table AS (
   SELECT * FROM (VALUES (1,  'B',   0, 899),
                         (2, 'kB', 900, 9e5 - 1),
                         (3, 'MB', 9e5, 9e8 - 1),
                         (4, 'GB', 9e8, 9e100)
                 ) t1 (id, unit, lo, hi)
   )
SELECT f.file_id, ROUND (f.bytes / POWER (1024, u.id - 1), 2) AS f_size, u.unit
   FROM dba_data_files f
   JOIN unit_table u
     ON f.bytes BETWEEN u.lo AND u.hi
  ORDER BY f.file_id;

This finally is a valid solution and is readable. The overall syntax is

 (VALUES (expression_list) [, (expression_list) ...]) name (column_list)

where expression_list is a comma speparated list of expressions, column_list is a comma separated list of column names. Numbers of expressions and columns must match, also the datatypes of the expressions.

Since this is a clause for subqueries, the value constructor can also be used in the create table statement:

CREATE TABLE my_table AS
   SELECT * FROM (
      VALUES (1, sysdate, 'nix'),
             (2, sysdate-1, 'hihi'),
             (3, NULL, 'also nix')
        ) t1 (id, datum, tx);

This looks convenient, but it has its perks. YOu may add a column list before the „AS“, but you still have to provide an additional one at the end of the value constructor. If both are provided, the first one wins.

Also: You cannot define the datatypes for the new table, they are derived from the value constructor. This is not that bad, but unfortunately also the precision gets defined by this clause, so we end up with that:

DESC my_table
 Name    Null?    Type
 ------- -------- -----------
 ID               NUMBER
 DATUM            DATE
 TX               VARCHAR2(8)

The precision of column TX is taken from the maximum length of its data. If you want to add additional data into that table, you either have to stick to that maximim length or change it – which is of course a simple thing:

ALTER TABLE my_table MODIFY (tx VARCHAR2(100));

You might also use the value constructor in INSERTs:

INSERT INTO my_table 
  VALUES (4, NULL,'another set of data'),
         (5, TO_DATE('19700101','yyyymmdd'), 'The Epoch');

2 rows created.

Especially if you can insert several rows – eg all lines items of an order – it even will be much faster, since it is only one statement which has to be parsed and executed. This means you get bulk operation performance with a simple to write INSERT!

or in UPDATEs:

UPDATE employees e
   SET e.salary = e.salary + u.sal_inc
   FROM (VALUES (30, 100),
                (50, -100))
              u (dep, sal_inc)
   WHERE e.department_id = u.dep;

Here we also make use of the 23c new feature „FROM clause in UPDATEs“.

Conclusio: Oracle 23c makes life easier for developers when it comes to create on-the-fly data.