In our recent posting “Oracle Database 23c Free Developer Release – 10 features you should know” my colleagues and I introduced 10 interesting SQL features. Now we want to deepen some of these features and give some more details and background information. In this posting I’d like to focus on the new VALUE constructor and give some examples.
 
Starting with Oracle 23c, the VALUE constructor has been extended so that you can now insert several rows in a single INSERT statement.

This constructor can also be used in SELECT statements, to leverage queries or even query factorizations without the need to use the DUAL table.

Let’s show some examples to get an idea how this extended VALUE constructor can be used.

First, let’s see it in action with an INSERT or CREATE as SELECT statement:

create table cccp as
SELECT *
FROM (VALUES (1,'SCOTT'),
             (2,'SMITH'),
             (3,'JOHN')
) t1 (employee_id, first_name)
where 1=2;

In this example, we created a table on the fly using the new VALUE constructor. This table has no rows, as the predicate “where 1=2” is used to create an empty table.

Let’s review the structure of the table:

desc cccp
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID					    NUMBER
 FIRST_NAME					    VARCHAR2(5)

Note that the “FIRST_NAME” column was created as a VARCHAR2(5), according the the data that was meant to be inserted. Actually 5 is the maximum length of the values that were to be inserted in the column. This might not be enough, hence we might want to cast to the right format:

drop table cccp purge;
--- CAST the values to get the correct type
create table cccp
as
SELECT *
FROM (VALUES (1,'SCOTT'),
             (2,cast('SMITH' as VARCHAR2(30))),
             (3,'JOHN')
) t1 (employeed_id,first_name)
where 1=2;

Now describe the table:

desc cccp
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEED_ID					    NUMBER
 FIRST_NAME					    VARCHAR2(30)

Now that we have the correct format, insert new rows in the table, using the new VALUE constructor:

-- Insert several tuples in one DML:

insert into cccp (EMPLOYEED_ID,FIRST_NAME)
values (1,'SCOTT'),
        (2,'SMITH'),
        (3,'JOHN');

3 rows created.

SQL> commit;

Commit complete.

SQL> select * from cccp;

EMPLOYEED_ID FIRST_NAME
------------ ------------------------------
	   1 SCOTT
	   2 SMITH
	   3 JOHN

Now let’s use the VALUE constructor in SELECT statements. In the next example, observe that this leverages a kind of table function on the fly !

SELECT *
FROM (VALUES (1,'SCOTT'),
             (2,'SMITH'),
             (3,'JOHN')
) t1 (employee_id, first_name);

EMPLOYEE_ID FIRST
----------- -----
	  1 SCOTT
	  2 SMITH
	  3 JOHN

Observe that we name the pseudo-columns using a column list just after “t1” alias.

This is especially powerful when we use query factorization:

with CCCP (c1,c2) as (
  values (1, 'SCOTT'), (2, 'SMITH'), (3, 'JOHN'))
select * from cccp;

	C1 C2
---------- -----
	 1 SCOTT
	 2 SMITH
	 3 JOHN

Before 23c, we would have achieved the same result with:

with CCCP as
(
  select c1,c2 from (
    select 1 as c1,'SCOTT' as c2 from dual
    union
    select 2 as c1, 'SMITH' as c2 from dual
    union
    select 3 as c1, 'JOHN' as c2 from dual
  )
)
select * from cccp;

	C1 C2
---------- -----
	 1 SCOTT
	 2 SMITH
	 3 JOHN

The new VALUE constructor leverages a much simpler syntax, either for SELECT statements or for INSERT/CREATE as SELECT statements. That’s why I included it in my favorite developer features in Oracle 23c. I strongly encourage you to start testing it on Oracle 23c Free !

 

Further Reading