Oracle 23ai (23.9) — Introducing Non-Positional INSERT INTO SET Clause

If you are familiar with Oracle Database, you know the basic INSERT INTO command. Let me briefly remind you of its traditional usage with some simple examples:

SQL> CREATE TABLE vahid.tbl (
      id NUMBER,
      name VARCHAR2(100),
      last_name VARCHAR2(100)
    );
Table created.

SQL> INSERT INTO vahid.tbl VALUES (1, 'VAHID', 'YOUSEFZADEH');
1 row created.

SQL> INSERT INTO vahid.tbl (name, last_name) VALUES ('VAHID', 'YOUSEFZADEH');
1 row created.

When working with tables containing many columns, this standard syntax can become difficult to read and maintain, as it is not always obvious which values correspond to which columns.

New Feature in Oracle 23ai (23.9): INSERT INTO ... SET Clause
Oracle 23ai (23.9) introduces a new syntax that simplifies the INSERT INTO command by allowing a SET clause similar to that used in UPDATE statements. For example:

SQL> INSERT INTO vahid.tbl SET id = 1, name = 'VAHID', last_name = 'YOUSEFZADEH';
1 row created.

As you can see, this syntax removes the need for parentheses around column-value pairs, improving readability.

**Limitation: Multiple Row Inserts
**However, this new syntax currently does not support inserting multiple rows without parentheses. To insert multiple rows, you must still use parentheses as follows:

SQL> INSERT INTO vahid.tbl SET
      (id = 1, name = 'VAHID', last_name = 'YOUSEFZADEH'),
      (id = 2, name = 'Bahman', last_name = 'Garosi');
2 rows created.

The BY NAME Clause for Subquery Inserts
Another valuable addition in Oracle 23.9 is the BY NAME clause, which simplifies inserting data from a subquery. Traditionally, when inserting rows from a subquery, the order of columns in the SELECT statement must match the order of columns in the target table. This can be error-prone and cumbersome.

With the BY NAME clause, Oracle matches columns by name or alias rather than by position. This flexibility allows columns to be listed in any order.

Example 1:

SQL> CREATE TABLE vahid.tbl (
id NUMBER,
name VARCHAR2(100),
last_name VARCHAR2(100)
);
Table created.

SQL> CREATE TABLE vahid.tbl2 (
id NUMBER,
last_name VARCHAR2(100),
name VARCHAR2(100)
);
Table created.

SQL> desc vahid.tbl;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(100)
 LAST_NAME                                          VARCHAR2(100)

SQL> desc vahid.tbl2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 LAST_NAME                                          VARCHAR2(100)
 NAME                                               VARCHAR2(100)

SQL> INSERT INTO vahid.tbl SET id = 1, name = 'VAHID', last_name = 'YOUSEFZADEH';

1 row created.

SQL> INSERT INTO vahid.tbl2 BY NAME SELECT id, name, last_name FROM vahid.tbl;

1 row created.

SQL> SELECT * FROM vahid.tbl;

        ID NAME       LAST_NAME
---------- ---------- -----------
         1 VAHID      YOUSEFZADEH

SQL> SELECT * FROM vahid.tbl2;

        ID LAST_NAME   NAME
---------- ----------- ----------
         1 YOUSEFZADEH VAHID

ٍExample 2:

SQL> INSERT INTO vahid.tbl SET id = 1, name = 'VAHID', last_name = 'YOUSEFZADEH';

1 row created.

SQL>  insert into vahid.tbl2(id,name,last_name)  BY NAME SELECT name,id,   last_name FROM vahid.tbl;

1 row created.

SQL> SELECT id,name,last_name FROM vahid.tbl;

        ID NAME       LAST_NAME
---------- ---------- -----------
         1 VAHID      YOUSEFZADEH

SQL> SELECT id,name,last_name FROM vahid.tbl2;

        ID NAME       LAST_NAME
---------- ---------- -----------
         1 VAHID      YOUSEFZADEH

Example 3:

SQL> drop table vahid.tbl;

Table dropped.

SQL> drop table vahid.tbl2;

Table dropped.

SQL> CREATE TABLE vahid.tbl (
id2 NUMBER,
name VARCHAR2(100),
last_name VARCHAR2(100)
);  

Table created.

SQL> CREATE TABLE vahid.tbl2 (
id NUMBER,
last_name VARCHAR2(100),
name VARCHAR2(100)
); 

Table created.

SQL> INSERT INTO vahid.tbl SET id2 = 1, name = 'VAHID', last_name = 'YOUSEFZADEH';

1 row created.

SQL> INSERT INTO vahid.tbl2(id,name,last_name) BY NAME SELECT id2, name, last_name FROM vahid.tbl;

ERROR at line 1:
ORA-63877: The explicit column list does not exactly match the exposed column
names.
Help: https://docs.oracle.com/error-help/db/ora-63877/