VALUE constructor in Oracle Database 23ai/26ai

Oracle Database 23ai (and 26ai) introduces table value constructors using the VALUES clause.
This lets you define inline row sets directly in SQL—no DUAL, no UNION ALL, no temporary tables.

You can use VALUES in SELECT, INSERT, UPDATE, MERGE, and WITH clauses.

Lets create some test table

DROP TABLE IF EXISTS test_tbl;

CREATE TABLE test_tbl
(
  user_id   NUMBER,
  user_name VARCHAR2(10)
);

The most simple way to generate data with constructor VALUE

SELECT constructor_column1, constructor_column2
FROM 
(
    VALUES ('tomasz10', 17), ('tomasz20', 22)
) AS my_constructor(constructor_column1, constructor_column2);

CONSTRUCTOR_COLUMN1 CONSTRUCTOR_COLUMN2 
------------------- ------------------- 
tomasz10 17 
tomasz20 22

In previous version it required a lot of UNION ALL

SELECT constructor_column1, constructor_column2
FROM (
  SELECT 
    'tomasz10' AS constructor_column1, 
    17 AS constructor_column2 
  FROM dual UNION ALL
  SELECT 'tomasz20', 22 FROM dual
);

INSERT example

INSERT INTO test_tbl(user_id, user_name)
VALUES
  (10, 'tomasz10'), 
  (20, 'tomasz20'),  
  (30, 'tomasz30'), 
  (40, 'tomasz40'); 
  
COMMIT;

SELECT * FROM test_tbl;

   USER_ID USER_NAME 
---------- ----------
        10   tomasz10  
        20   tomasz20  
        30   tomasz30  
        40   tomasz40

One statement, many rows. Cleaner than INSERT ALL.

UPDATE example

UPDATE test_tbl
SET user_id = 15
WHERE user_name IN (
    SELECT constructor_column
    FROM (
        VALUES ('tomasz10'), ('tomasz20')
    ) AS my_constructor(constructor_column)
);

SELECT * FROM test_tbl;

   USER_ID USER_NAME 
---------- ----------
        15 tomasz10  
        15 tomasz20  
        30 tomasz30  
        40 tomasz40

Use VALUES as an inline collection—no PL/SQL types needed.

MERGE example

MERGE INTO test_tbl target
USING (
    VALUES
        (30, 'Albert'),
        (105, 'Smartwatch')
) source (id,name)
ON (target.user_id = source.id)
WHEN MATCHED THEN
    UPDATE SET target.user_name = source.name
WHEN NOT MATCHED THEN
    INSERT (user_id, user_name)
    VALUES (source.id, source.name);

COMMIT;

SELECT * FROM test_tbl;

   USER_ID USER_NAME 
---------- ----------
        15 tomasz10  
        15 tomasz20  
        30 Albert    
        40 tomasz40  
       105 Smartwatch

Perfect for small batch upserts and deployment scripts.

WITH example

WITH w_a(ID,NAME) AS
(
  VALUES
  (10, 'tomasz10'), 
  (20, 'tomasz20'),  
  (30, 'tomasz30'), 
  (40, 'tomasz40') 
)
SELECT * FROM w_a;

SELECT * FROM test_tbl;

        ID NAME    
---------- --------
        10 tomasz10
        20 tomasz20
        30 tomasz30
        40 tomasz40

Readable inline datasets—finally no SELECT ... FROM dual UNION ALL.

Have a fun 🙂

Tomasz

DBAORA

oracle expert presents

VALUE constructor in Oracle Database 23ai/26ai

INSERT example

UPDATE example

MERGE example

WITH example

Leave a Reply Cancel reply